Date comparison between same data type in sql query-CodePudding

I have a column in the format -

MAX_DATE                                PROCESS_DATE
2022-10-14T16:09:09.272 00:00           2022-11-08T15:43:29.027 00:00

WHEN I AM WRITING THE CONDITION

SELECT * FROM ANC 
WHERE MAX_DATE > PROCESS_DATE

Even then a lot of rows are coming although they do not fulfill the condition. The date format of both is the same, i am not sure how to change both of them in the same date format, i tried the below but still I am getting all rows so the above condition is not working

SELECT * FROM ANC 
WHERE TO_DATE(to_char(MAX_DATE,'DD-MM-YYY'),'DD-MM-YYY') > TO_DATE(to_char(PROCESS_DATE,'DD-MM-YYY'),'DD-MM-YYY')

Also this is giving me the output like

MAX_DATE                                PROCESS_DATE
2022-10-14T00:00:00.000 00:00           2022-11-08T00:00:00.000 00:00

How to compare both the dates ?

CodePudding user response：

please try with below query

where 
convert(datetime, MAX_DATE)  >= convert(datetime,PROCESS_DATE)

CodePudding user response：

If your columns have a TIMESTAMP WITH TIME ZONE data type then just compare the columns directly:

SELECT *
FROM   ANC 
WHERE  MAX_DATE > PROCESS_DATE

If your columns have the VARCHAR2 data type then, firstly, that is bad practice and you should alter the table and change the data type to TIMESTAMP WITH TIME ZONE, and secondly, if you are going to keep them as strings then you need to convert them to timestamps using the TO_TIMESTAMP_TZ function before comparing them:

SELECT *
FROM   ANC 
WHERE  TO_TIMESTAMP_TZ(MAX_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
         > TO_TIMESTAMP_TZ(PROCESS_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')