CODEWITHSUNDEEP
sqloracle-database
BnJ
BnJ

Reputation: 1044

Comparison between a date and a date converted in char

In Oracle Database, can you tell me why this query return FALSE ?

select 
case 
  when to_date('01/01/1900','DD/MM/YYYY') >= (to_date(to_char(to_date('01/01/1900','DD/MM/YYYY')))) then 'TRUE'
  else 'FALSE'
end
from DUAL;

And how can I do to have TRUE ?

I want to compare two identical dates, one is a varchar2 and the other is a date.

Upvotes: 0

Views: 619

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think the answer is clear if you run:

select to_date('01/01/1900','DD/MM/YYYY'),
       to_date(to_char(to_date('01/01/1900','DD/MM/YYYY')))
from DUAL;

On SQL Fiddle, this returns:

January, 01 1900 00:00:00   January, 01 2000 00:00:00

The problem is that the default date format for Oracle uses 2-digit years: 01-JAN-00. You can fix this by including explicit formats at each step:

select to_date('01/01/1900','DD/MM/YYYY'),
       to_date(to_char(to_date('01/01/1900','DD/MM/YYYY'), 'DD/MM/YYYY'), 'DD/MM/YYYY')
from dual;

Upvotes: 3

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

If you want it to be the same, use the same format for all functions :

select 
case 
  when to_date('01/01/1900','DD/MM/YYYY') >= 
  (to_date(to_char(to_date('01/01/1900','DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY')) then 'TRUE'
  else 'FALSE'
end
from DUAL;

Upvotes: 3

Related Questions