How String to Timestamp works without explicit conversion in oracle where clause

user_9890577 0
Viewed 124

Both of following SQL works in my Oracle DB, the result or the count of results are completely same and correct. CREATE_TIME is a timestamp column.

select * from EMPLOYEE where CREATE_TIME =(or >) '2022-01-21 2:49:38.251'
select * from EMPLOYEE where CREATE_TIME =(or >) '22-01-21 2:49:38.251000'

Quite curious on how it works because the String format is different, I didn't use TO_* conversion function and the Oracle NLS(as default conversion rules) shows completely different format.

PARAMETER -> VALUE
NLS_TIMESTAMP_TZ_FORMAT -> DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT -> HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT -> DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT -> HH.MI.SSXFF AM

Searched the information but didn't find the answer, it would be appreciate if anyone could answer me and provide a information/document link for reference.

timestamp oracle sql
1 Answers

If you try to perform an explicit cast using CAST(value AS TIMESTAMP), or an equivalent implicit cast such as in your query, from the string values to a timestamp then Oracle will implicitly convert the cast to the equivalent of:

TO_TIMESTAMP(
  value,
  (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TIMESTAMP_FORMAT')
)

If you have the sample data:

CREATE TABLE table_name (value) AS
SELECT '2022-01-21 2:49:38.251'  FROM DUAL UNION ALL
SELECT '22-01-21 2:49:38.251000' FROM DUAL;

You can see it in action using:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH24.MI.SSXFF';

SELECT value,
       TO_CHAR(
         CAST(value AS TIMESTAMP),
         'YYYY-MM-DD HH24:MI:SS.FF'
       ) AS converted_value
FROM   table_name;

Outputs the error:

ORA-01843: not a valid month

As, given the string-to-date conversion rules the MON format model will also match MONTH but it will not match the numeric MM format so the 01 month generates the error.

However:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'RR-MM-DD HH24:MI:SSXFF'

SELECT value,
       TO_CHAR(
         CAST(value AS TIMESTAMP),
         'YYYY-MM-DD HH24:MI:SS.FF'
       ) AS converted_value
FROM   table_name;

Outputs:

VALUE CONVERTED_VALUE
2022-01-21 2:49:38.251 2022-01-21 02:49:38.251000
22-01-21 2:49:38.251000 2022-01-21 02:49:38.251000

Both the rows are converted as expected.

If, instead, you use:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF'

SELECT value,
       TO_CHAR(
         CAST(value AS TIMESTAMP),
         'YYYY-MM-DD HH24:MI:SS.FF'
       ) AS converted_value
FROM   table_name;

Then the output is:

VALUE CONVERTED_VALUE
2022-01-21 2:49:38.251 2022-01-21 02:49:38.251000
22-01-21 2:49:38.251000 0022-01-21 02:49:38.251000

And the first row converts as expected but the second row the YYYY format model matches 22 and gives 22 AD rather than 2022 AD (as was probably expected).

If you want to compare to a timestamp then either use an explicit conversion:

SELECT *
FROM   EMPLOYEE
WHERE  CREATE_TIME = TO_TIMESTAMP(
                       '2022-01-21 2:49:38.251',
                       'YYYY-MM-DD HH24:MI:SSXFF'
                     )

Or a timestamp literal:

SELECT *
FROM   EMPLOYEE
WHERE  CREATE_TIME = TIMESTAMP '2022-01-21 02:49:38.251'

If you rely on the NLS parameters then your query may have different (and unexpected) behaviours for different sessions (sometimes even for different sessions of the same user).

db<>fiddle here

user_15092640
Related