I have a table with a column of type timestamp without time zone.
I want to select that column with the mmm yyyy format – for example, “Mar 2011”. How to format it that way? I tried:
select cast(now() as date)
but it is giving me the incorrect format.
5 Answers
SELECT TO_CHAR(NOW(), 'Mon YYYY');
DateAndTime Reformat:
SELECT *, to_char( last_update, 'DD-MON-YYYY') as re_format from actor;
DEMO:
You need to use a date formatting function for example to_char
I think in Postgres you can play with formats for example if you want dd/mm/yyyy
TO_CHAR(submit_time, 'DD/MM/YYYY') as submit_date
You can write your select query as,
select * from table_name where to_char(date_time_column, 'YYYY-MM') = '2011-03';
