5

I have to convert a SYSDATE date to a specific date format. That format must be something like this: '2016-11-23T15:12:48Z'. I think this is a weird date format but is the requirements that I have.

This must be a date to send in a Web Service message.

In Oracle (12c or 11g) I have some function to transform a date in this specific format? Thanks.

milheiros
  • 621
  • 2
  • 14
  • 34
  • 2
    [to_char()](https://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129) - it's not a "weird" format though. This is an ISO format that includes the timezone –  Nov 28 '16 at 10:03
  • 3
    If I recall correctly it's the ISO-8601 standard but, well, some people even complaint about the metric system :) – Álvaro González Nov 28 '16 at 10:05

2 Answers2

10

That will give you ISO-8601 mentioned in comment:

select to_char(systimestamp,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') isodt from dual;

If you really want Z instead of timezone you can use:

    select to_char(cast(systimestamp as timestamp) at time zone 'UTC',
               'yyyy-mm-dd"T"hh24:mi:ss"Z"')
    from dual;
Kacper
  • 4,798
  • 2
  • 19
  • 34
  • The `Z` post script on the ISO-8601 indicates that the timestamp is from the UTC time zone (Zulu-time). If `SYSDATE` is not UTC then it will need to be converted to the correct time zone. – MT0 Nov 28 '16 at 10:49
  • @MT0 Thank you. I haven't known that. I've added cast to UTC to amend it. – Kacper Nov 28 '16 at 10:52
  • @Kacper or you could use the pre-defined `sys_extract_utc()` function which does the work for you. – Boneist Nov 28 '16 at 10:57
  • @Boneist Yes I've seen your answer. Thank you. – Kacper Nov 28 '16 at 11:13
  • `TO_CHAR(CAST(SYSTIMESTAMP AS TIMESTAMP) AT TIME ZONE 'UTC', '...')` is wrong. This works only if your `SESSIONTIMEZONE` is equal to your database server time zone. You must use `TO_CHAR(CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS TIMESTAMP), '...')` – Wernfried Domscheit Nov 28 '16 at 13:59
4

Since the "Z" in the timestamp format you're after means "This is in UTC", you should first make sure your sysdate is returned in UTC. You can do this by using systimestamp and sys_extract_utc() like so:

select to_char(sys_extract_utc(systimestamp), 'yyyy-mm-dd"T"hh24:mi:ss"Z"') dt_as_utc
from dual;

DT_AS_UTC
--------------------
2016-11-28T10:33:49Z
Community
  • 1
  • 1
Boneist
  • 22,910
  • 1
  • 25
  • 40