CODEWITHSUNDEEP
sqlpostgresqldate
Amit Sharad
Amit Sharad

Reputation: 1181

Changing date format for PostgreSQL query result

I have following query 

select substring(listDate from '............$') as v_end_date,
substring(listDate from '^...............') as v_start_date

Now listDate value can be like 

select substring('06 Jan 2014 to 12 Jan 2014,
 13 Jan 2014 to 19 Jan 2014,
 20 Jan 2014 to 26 Jan 2014
' from '............$') as v_end_date,
substring('06 Jan 2014 to 12 Jan 2014,
 13 Jan 2014 to 19 Jan 2014,
 20 Jan 2014 to 26 Jan 2014
' from '^............') as v_start_date

Above query results in 

V_END_DATE  V_START_DATE
26 Jan 2014 06 Jan 2014

Now I need to have v_end_date and v_start_date format like yyyy-mm-dd and like Mon 06 Jan 2014.

Upvotes: 1

Views: 6946

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

Convert your string to an actual date with to_date() and use to_char() to get pretty much any format you like.

Demo:

SELECT to_char(day, 'YYYY-MM-DD')     AS format1
     , to_char(day, 'Dy DD Mon YYYY') AS format2 
FROM   (SELECT to_date('26 Jan 2014', 'DD Mon YYYY') AS day) sub

Upvotes: 5

Related Questions