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'

CodePudding user response：

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;

CodePudding user response：

It works with varchar and timestamp data types:

CREATE TABLE tbl ("date" TIMESTAMP);
INSERT INTO tbl ("date") VALUES ('01.01.2022 12:53:21');
INSERT INTO tbl ("date") VALUES ('05.09.2022 21:01:18');
INSERT INTO tbl ("date") VALUES ('22.03.2022 20:13:10');
INSERT INTO tbl ("date") VALUES ('04.05.2022 21:00:00');

SELECT "date"::DATE
FROM tbl
ORDER BY "date"::DATE DESC

Returns: