Changing date format for PostgreSQL query result

Question

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.

Erwin Brandstetter · Accepted Answer · 2014-02-26 18:44:35Z

5

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

answered Feb 26, 2014 at 18:44

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Str. Over a year ago

Recommended. Never store dates in string format, you can not calculate dates and times with that.

Collectives™ on Stack Overflow

Changing date format for PostgreSQL query result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related