CODEWITHSUNDEEP
oracle-databaseoracle11g
mentongwu
mentongwu

Reputation: 473

Why to_char function may change date in oracle

I use select value,datetime,to_char(sensortime,'DD-MON-YYYY HH24:MI:SSxFF') datetime2 from test; I find datetime and datetime2 are different,How can I get the same result? enter image description here

Upvotes: 0

Views: 812

Answers (2)

MT0
MT0

Reputation: 167867

Why to_char function may change date in oracle

I does not change the date - it just changes how the date is formatted.

SQL/Plus and SQL Developer will typically use the NLS_TIMESTAMP_FORMAT session parameter to implicitly apply a format to a TIMESTAMP data type.

Your query is effectively:

SELECT value,
       TO_CHAR(
         sensortime,
         (
           SELECT VALUE
           FROM   NLS_SESSION_PARAMETERS
           WHERE  PARAMETER = 'NLS_TIMESTAMP_FORMAT'
         )
       ) AS datetime,
       to_char(sensortime,'DD-MON-YYYY HH24:MI:SSxFF') datetime2
FROM   test

Looking at the outputs, the NLS_TIMESTAMP_FORMAT is

'DD-MON-YYYY HH12:MI:SSxFF AM'

and your format is:

'DD-MON-YYYY HH24:MI:SSxFF'

Which is why they are giving different outputs (one is a 12--hour clock and the other is 24-hour clock).

How can I get the same result?

Change your query to match the NLS_TIMESTAMP_FORMAT:

SELECT value,
       sensortime,
       TO_CHAR(sensortime,'DD-MON-YYYY HH12:MI:SSxFF AM') datetime2
FROM   test

Or change the NLS_TIMESTAMP_FORMAT:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SSxFF'

and use your query.

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

Change your session NLS_TIMESTAMP_FORMAT as this:

alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SSxFF';

In case data type of your column is TIMESTAMP WITH TIME ZONE you have to use NLS_TIMESTAMP_TZ_FORMAT instead of NLS_TIMESTAMP_FORMAT

Upvotes: 1

Related Questions