Different behavior between Dbeaver and OSD clients with dates

January 18, 2022

I use Oracle Database 11g Release 11.2.0.4.0

When doing this query :

SELECT 
    CASE WHEN (DATE '2005-11-25') IN ('25/11/05') THEN
        'TRUE'
    ELSE
        'FALSE'
    END
FROM DUAL;

I get FALSE from Oracle SQL Developer 21.4.1.349 (French version) and TRUE from DBeaver 21.1.4.202108020335 (French version).

I understand that I compare a date with a string, so in my opinion the result should be FALSE, but that may me implementation-dependant.

How does it come that Oracle answers differently in those situations ? Does DBeaver do a preprocessing on dates before submitting the request?

>Solution :

Because you are comparing a date to a string, you are causing the string to be implicitly converted to a date, using your session’s NLS settings. Those may be derived from your locale, or may be set explicitly by your client, e.g. through preferences.

If NLS_DATE_FORMAT is is set to ‘DD/MM/YYYY’ then ’25/11/05′ is converted to 0005-11-25, so the comparison is false. If it is set to ‘DD/MM/YY’ (or ‘DD/MM/RR’, or ‘DD/MM/RRRR’) then it is converted to 2005-11-25, so the comparison is true.

db<>fiddle

You should not rely on implicit conversions, or NLS settings – since you can’t control the environment of whoever runs your code.