0

I am in need of experts' help here. I have a column "Actual_Date" of data type timestamp. It is in the format:

yyyy - mm - dd 00:00:00

I want to convert the column into the format:

mm - dd - yyyy

How can I convert the date?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jake Wagner
  • 786
  • 2
  • 12
  • 29

2 Answers2

7

The exact data type matters. If you have a timestamp column (timestamp without time zone) and want the date part as date, just cast:

SELECT my_timestamp::date;

You can do the same with timestamptz (timestamp with time zone), the resulting date depends on the time zone setting of your current session, though. The current "date" depends on where in the world you ask ...

Or you can use to_char() like a_horse provided, to get a text as result.

If it's just a matter of how date and timestamp are displayed, look at the datestyle setting:

To get reliable text representation, independent of settings, to_char() is the safe bet.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks Erwin, I retrieve my data as timestamp without timezone. Is there anyway I can possibly convert the date format as to mm-dd-yyyy permanently? – Jake Wagner Dec 15 '16 at 16:43
  • @Pythoner: values for `date` or `timestamp` columns are stored **without** a format. The format is applied when _displaying_ the values. You configure your SQL client if you want to display dates or timestamps in a different way. –  Dec 15 '16 at 18:40
2

You just need to format it the way you want using to_char() 

select to_char("Actual_Date", 'mm-dd-yyyy')
from the_table;

More details in the manual:
https://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE