CODEWITHSUNDEEP
sqloracle-database
user2907032
user2907032

Reputation: 467

Compare Date format with String using oracle

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.

Upvotes: 1

Views: 1218

Answers (1)

MT0
MT0

Reputation: 168681

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

Upvotes: 1

Related Questions