CODEWITHSUNDEEP
mysqldatabasepostgresqlpostgresql-9.1postgresql-9.0
Sathish
Sathish

Reputation: 4713

DATE_FORMAT in postgresql

I'm working in postgresql and I need to convert the date format in query itself, in mysql there is option called DATE_FORMAT and I can use a query like this:

Select DATE_FORMAT(date_time, '%b %e, %Y, %T') from table_name

is there any option in postgresql? Please let me know if any?

Upvotes: 25

Views: 71676

Answers (4)

Amrita
Amrita

Reputation: 611

If I modify your

Select DATE_FORMAT(date_time, '%b %e, %Y, %T') from table_name

to

Select DATE_FORMAT(now(), '%b %e, %Y, %T')

it will return Aug 21, 2012, 16:51:30.

You can do the same thing in Postgresql:

Select to_char(now(), 'Mon-dd-YYYY,HH24:mi:ss')

will return you Aug-21-2012,16:52:08

Hope your problem is sort out.

Upvotes: 45

long
long

Reputation: 4318

to_char, eg:

to_char(creation_date, 'FMDD.MM.YYYY, HH24:MI:SS') AS creation_date_f

Upvotes: 5

user330315
user330315

Reputation:

Use to_char():

http://www.postgresql.org/docs/current/static/functions-formatting.html

I don't know what '%b %e, %Y, %T' produces as an output (and you didn't supply a sample output), so I cannot give you the equivalent format mask for Postgres.

The following produces an ANSI date:

select to_char(date_time, 'yyyy-mm-dd hh24:mi:ss')
from table_name;

Upvotes: 18

Moyed Ansari
Moyed Ansari

Reputation: 8461

Try this 

SELECT to_char(date_time, 'dd.mm.YYYY') from table_name

Upvotes: 11

Related Questions