Forum Stats

  • 3,734,235 Users
  • 2,246,916 Discussions
  • 7,857,194 Comments

Discussions

TO_TIMESTAMP

i am trying to use this function to get the date which json supports ,

[code]

SELECT TO_UTC_TIMESTAMP_TZ('01-JAN-2021') FROM DUAL;

[/code]


i am getting the ORA-00904 as invalid identifier. how do i specify the dates in zulu format. couple of ways i tried in payload , but how to tell the consuming service people which format to use.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,394 Black Diamond

    TO_UTC_TIMESTAMP_TZ was introduced in Oracle 18C. Based on the error message you are on an older version. But that SQL you posted wouldn't work regardless:

    SQL> SELECT TO_UTC_TIMESTAMP_TZ('01-JAN-2021') FROM DUAL;
    SELECT TO_UTC_TIMESTAMP_TZ('01-JAN-2021') FROM DUAL
                                                   *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    
    SQL> 
    

    RTFM:

    This is the allowed syntax for dates and times:

    • Date (only): YYYY-MM-DD
    • Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
    SQL> SELECT TO_UTC_TIMESTAMP_TZ('2021-01-01') FROM DUAL;
    
    TO_UTC_TIMESTAMP_TZ('2021-01-01')
    ---------------------------------------------------------------------------
    01-JAN-21 12.00.00.000000000 AM +00:00
    
    SQL>
    


    SY.

  • Arif2018
    Arif2018 Member Posts: 206 Red Ribbon
    Thanks Solomon, but what is the equivalant of  TO_UTC_TIMESTAMP_TZ in Oracle 12c. 
    


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,394 Black Diamond

    Use:

    SQL> SELECT TO_TIMESTAMP_TZ('2021-01-01 00:00:00 00:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM DUAL;
    
    
    TO_TIMESTAMP_TZ('2021-01-0100:00:0000:00','YYYY-MM-DDHH24:MI:SSTZH:TZM')
    ---------------------------------------------------------------------------
    01-JAN-21 12.00.00.000000000 AM +00:00
    
    
    SQL>
    

    SY.

  • Arif2018
    Arif2018 Member Posts: 206 Red Ribbon

    Thanks solomon, is it possible to get the datatime as output in zulu format as i need the time T seperator which seperates the time portion from date and z in the last so that i can use to create a payload.

    for example "2018-06-26T06:01:40Z".

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown

    I'm assuming here that your input is of the DATE type and supposed to be in the timezone of the database:

    select to_char(from_tz(cast(date '2021-01-01' as timestamp),dbtimezone) at time zone 'UTC' ,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as ts from dual
    
    TS                  
    --------------------
    2020-12-31T23:00:00Z
    

    Since my dbtimezone is +01:00 the result in UTC is one hour earlier.

  • User_E74PC
    User_E74PC Member Posts: 2 Employee

    select  TO_UTC_TIMESTAMP_TZ( '2020-12-16T11:25:05.171Z') utc_to_date from dual;

    Getting the same error "ORA-00904: "TO_UTC_TIMESTAMP_TZ": invalid identifier" . Running the query on 12c.

    Have to convert this value '2020-12-16T11:25:05.171Z' to a date format. Is there way to convert in 12c.

    Thank you.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,394 Black Diamond

    TO_UTC_TIMESTAMP_TZ was introduced in 18C. In 12C use:

    select  TO_TIMESTAMP_TZ(REPLACE('2020-12-16T11:25:05.171Z','Z',' 00:00Z'),'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM"Z"') utc_to_date from dual
    /
    
    UTC_TO_DATE
    ---------------------------------------------------------------------------
    16-DEC-20 11.25.05.171000000 AM +00:00
    
    SQL>
    

    SY.

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown

    Alternatively the XML functions can be used:

    with input(ts) as (select '2020-12-16T11:25:05.171Z' from dual)
    select xmlcast(xmlelement(ts,ts) as timestamp with time zone) as ts from input
    
    TS                                 
    -----------------------------------
    16-DEC-20 11.25.05.171000000 AM GMT
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,394 Black Diamond
    edited Jun 9, 2021 4:21PM

    Or:

    with input(ts) as (select '2020-12-16T11:25:05.171Z' from dual)
    select xmlcast(xmlquery('.' passing ts returning content) as timestamp with time zone) as ts from input
    /
    
    TS
    ---------------------------------------------------------------------------
    16-DEC-20 11.25.05.171000 AM +00:00
    
    SQL>
    

    But REPLACE is more efficient than XML.

    SY.

    User_E74PC
  • User_E74PC
    User_E74PC Member Posts: 2 Employee

    Thank you Solomon. REPLACE works good.

Sign In or Register to comment.