1

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?

porthfind
  • 1,581
  • 3
  • 17
  • 30
  • 1
    What does "date2 is not a varchar2 but has other any date time format" mean? You can either have a string in a particular format that represents a date, in which case you'd have a `varchar2` or you can have a `date` or a `timestamp` neither of which has a format. – Justin Cave Mar 22 '16 at 21:22
  • Note `Z` means time zone `UTC` - is this the case or do you need a conversion? – Wernfried Domscheit Mar 22 '16 at 21:26
  • @WernfriedDomscheit yes it is time zone UTC...I want to convert to it having a varchar2 as 'variable1' – porthfind Mar 22 '16 at 21:27
  • @JustinCave I have edited my post to be more clearly what I want to do – porthfind Mar 22 '16 at 21:31
  • I'm still confused. Do you want `variable2` to be a `varchar2`? A `date`? A `timestamp with time zone`? Something else? A `varchar2` has a format. A `date` or a `timestamp` does not. You say "variable2 is not a varchar2 but has other any date time format" but that still doesn't make sense. – Justin Cave Mar 22 '16 at 21:35
  • See [How are dates stored in Oracle?](http://stackoverflow.com/a/13568348/1509264) for details of what a `DATE` is in Oracle and you will see that asking it to have a specific format is nonsensical. If you want a specific format then you will need to convert it to a string or tell your client software how to convert it to a string (usually with the `NLS_DATE_FORMAT` parameter - but this is a user variable and subject to change). – MT0 Mar 22 '16 at 21:37

3 Answers3

2

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
MT0
  • 143,790
  • 11
  • 59
  • 117
  • ISODATE will be a varchar, won't have a date time format..right? – porthfind Mar 22 '16 at 21:26
  • See my edit - dates do not have a format (they are represented internally by either 7 or 8 bytes, depending on how they are created). If you want a date to have a format then you need to convert it to a string (either within oracle or within the client you are using to perform the queries). – MT0 Mar 22 '16 at 21:29
0

Use below code:

Select to_char (current_timestamp,'yyyy-MM-dd"T"HH:mm:sss"Z"') from dual;
Rahul Agarwal
  • 4,034
  • 7
  • 27
  • 51
Anand Venkatesh
  • 1
0

In the case of having this result, I used this

TO_char(date,'YYYY-MM-DD"''T''"HH24:MI:SS."000-0500"') on datagrip with Oracle source.

Mu result: 2020-03-01'T'23:40:34.000-0500

Martin Taco
  • 51
  • 1