TO_TIMESTAMP

Arif2018

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.

Solomon Yakobson

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

Thanks Solomon, but what is the equivalant of  TO_UTC_TIMESTAMP_TZ in Oracle 12c. 

Solomon Yakobson

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

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

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

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

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

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

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

Thank you Solomon. REPLACE works good.