Issue with date in oracle

Question

I am trying to pull date from one table which is in ISO format and then store it in US date format?

Trunc(cast(to_timestamp(ATTRIBUTE_39,'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"')as date))

Actual output:

4/11/2018  12:00:00 AM

expected output:

4/11/2018

Without knowing exactly what you mean by "a date in ISO format" or "a date in US date format" I don't think anyone can answer this question.Is `ATTRIBUTE_39` a `date`, a `timestamp` or a `varchar2`? What is the type of the column you are inserting into? — William Robertson, Apr 12 '18 at 12:33

score 2 · Answer 1 · answered Apr 11 '18 at 12:18

I am trying to pull date from one table which is in ISO format and then store it in US date format?

Dates (and timestamps) do not have a format - they are represented in a table by 7 bytes for a date or 20 bytes for a timestamp.

You can get the value as a DATE data type using TO_TIMESTAMP_TZ and either the TZR or TZH:THM format models to match the time zone region/offset (they will both work with the Zulu time zone region)

SELECT CAST( 
         TO_TIMESTAMP_TZ(
           ATTRIBUTE_39,
           'yyyy-mm-dd"T"hh24:mi:ss.ff3TZH:TZM'
         ) AT TIME ZONE 'UTC'               -- Convert to a common time zone
         AS DATE
       )
FROM   your_table;

When you select from the table then whatever client program you are using (typically) will implicitly convert the 7-bytes it uses internally to something you, the user, can read - a string. SQL/Plus and SQL Developer use the NLS_DATE_FORMAT session parameter as the format model when they perform this implicit conversion.

So your query is effectively converted to:

SELECT TO_CHAR(
         CAST( 
           TO_TIMESTAMP_TZ(
             ATTRIBUTE_39,
             'yyyy-mm-dd"T"hh24:mi:ss.ff3TZR'
           ) AT TIME ZONE 'UTC'
           AS DATE
         ),
         (
           SELECT VALUE
           FROM   NLS_SESSION_PARAMETERS
           WHERE  PARAMETER = 'NLS_DATE_FORMAT'
         )
       )
FROM   your_table;

If you want to format a date or a timestamp then you will have to explicitly convert it to a string using TO_CHAR():

SELECT TO_CHAR(
         CAST( 
           TO_TIMESTAMP_TZ(
             ATTRIBUTE_39,
             'yyyy-mm-dd"T"hh24:mi:ss.ff3TZR'
           ) AT TIME ZONE 'UTC'
           AS DATE
         ),
         'MM/DD/YYYY'
       )
FROM   your_table;

Or by altering the NLS_DATE_FORMAT session parameter:

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

(Be aware that this will only change the format in the current session and will not change it for any other sessions/users.)

score 0 · Answer 2 · answered Apr 11 '18 at 12:02

0

Try this:

select to_char(Trunc(cast(to_timestamp('2016-06-29T13:13:00.123','yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"')as date)),'MM/DD/YYYY') from dual

the default date/time formatting depends on your NLS_DATE_FORMAT setting.

answered Apr 11 '18 at 12:02

OldProgrammer

12,050
4
24
45

Issue with date in oracle

2 Answers2