I am comparing strings with date oracle using the following sql but I am getting incorrect results. I tried with to_date, to_char options but didn't get any proper result.
select order from table where Date1 <> to_char(Date2, 'DD-MON-YYYY HH24:MI');
where Date1 : 10-JUN-21 01.00.00.000000000 AM Date2: 10-JUN-2021 01:00
The above sql returns the rows for above mentioned Date1 and Date2 although it is equal. Also Date1 is varchar format and Date2 is TIMESTAMP(6) WITH LOCAL TIME ZONE format.
Given your table and data:
Date1 is varchar format and Date2 is TIMESTAMP(6) WITH LOCAL TIME ZONE format.
CREATE TABLE table_name (
date1 VARCHAR2(20),
date2 TIMESTAMP(6) WITH LOCAL TIME ZONE
);
INSERT INTO table_name (
date1,
date2
) VALUES (
'10-JUN-2021 01:00',
TIMESTAMP '2021-06-10 01:00:00.000000'
);
You can convert DATE1 to a TIMESTAMP and compare:
SELECT *
FROM table_name
WHERE TO_TIMESTAMP( Date1, 'DD-MON-YYYY HH24:MI' ) <> Date2;
Or, convert DATE2 to a string:
SELECT *
FROM table_name
WHERE Date1 <> TO_CHAR( Date2, 'DD-MON-YYYY HH24:MI' );
Which both output zero rows:
DATE1 DATE2
db<>fiddle here
:SS to the format model when you convert to/from a string.