Search code examples
sqlpostgresqldatecasting

Postgres Date Cast from a String

I have a Date/time in the format DD-MON-YYHH24MISS. I have to cast this as date and then sort the data by date in desc order. The following query is giving erroneous results:

SELECT to_date(substring(td."date", 1,10), 'dd-MON-yy') as date from tbl order by date desc;

Whats the error here? I get dates as '2122-01-08'

Solution

  • You should be taking a substring of the first nine, not ten, characters:

    SELECT TO_DATE(SUBSTRING("date", 1, 9), 'DD-MON-YY') AS date
FROM tbl
ORDER BY date DESC;