Different behavior between Dbeaver and OSD clients with dates

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 1:

It depends on the NLS_DATE_FORMAT the session is using.

Oracle will implicitly convert your query to the equivalent of:

SELECT CASE WHEN DATE '2005-11-25' IN (
              TO_DATE(
                '25/11/05',
                (SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT')
              )
            )
       THEN 'TRUE'
       ELSE 'FALSE'
       END
FROM   DUAL;

As it relies on an implicit conversion from string to date so Oracle will use the session's date format in the conversion.

For example:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/RR';

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

Outputs: TRUE

But:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

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

Outputs: FALSE

You can see why using:

SELECT TO_CHAR(CAST('25/11/05' AS DATE), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Which outputs 0005-11-25 00:00:00 and is the 1st century, and not the 21st century, as the implicit conversion uses the DD/MM/YYYY HH24:MI:SS format model and expects a 4-digit year and gets the input 05 and assumes it to be a correct value of 5 AD rather than the 5th year of the current century (which you would get with the YY format model).

The best solution is to not rely on implicit string-to-date conversions.

Either using date literals:

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

Or, providing an explicit format model for the conversion:

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

db<>fiddle here

Solution 2:

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.