Oracle: convert varchar2 to the datetime format ‘yyyy-mm-ddThh:mm:ssZ’

datetimeoracle

I have a date in a varchar2 with format 'ddmmyyyy' and I want to convert it to the format 'yyyy-mm-ddThh:mm:ssZ' in which the final variable has a date time format.

For instance, I have the following varchar2 variable variable1:

variable1 := '11092016';

and I want the final result be:

variable2:='2016-09-11T00:00:00Z'

where variable2 is not a varchar2 but has other any date time format.

Can anyone help me, please?

Best Answer

Dates do not have a format. The format is either applied by converting them to a string or by the client software. If you just want it as a date then use TO_DATE( value, 'DDMMYYYY' ) (and the date object will have a time component set to 00:00:00) but if you want it as an ISO8601 formatted string:

Oracle Setup:

CREATE TABLE dates ( value ) AS
SELECT '20032016' FROM DUAL;

Query:

SELECT TO_CHAR(
         TO_DATE( value, 'DDMMYYYY' ),
         'YYYY-MM-DD"T"HH24:MI:SS"Z"'
       ) AS ISODate
FROM   dates;

Output:

ISODATE
--------------------
2016-03-20T00:00:00Z
Related Topic