0

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 ?

MT0
  • 143,790
  • 11
  • 59
  • 117
SSA_Tech124
  • 577
  • 1
  • 9
  • 25
  • What is the data type of each column? `SELECT TABLE_NAME , COLUMN_NAME , DATA_TYPE , DATA_LENGTH , DATA_PRECISION , DATA_SCALE , NULLABLE FROM ALL_tab_columns WHERE COLUMN_NAME IN ('MAX_DATE', 'PROCESS_DATE')` – dougp Nov 09 '22 at 22:18

2 Answers2

1

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')
MT0
  • 143,790
  • 11
  • 59
  • 117
0

please try with below query

where 
convert(datetime, MAX_DATE)  >= convert(datetime,PROCESS_DATE)
mark james
  • 3
  • 4
  • 1
    In Oracle, the [`CONVERT` function](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions027.htm) changes strings from one character set to another character set; how does that help the OP solve their problem? – MT0 Nov 04 '22 at 00:32