CODEWITHSUNDEEP
oracle-databasedatetimeutc
tia97
tia97

Reputation: 350

Convert Date Field to UTC in Oracle

I am trying to convert a date field 'createdate' to UTC time but the ask is to be -04:00. I have tried a bunch of things and have had no success. I am working on Oracle. Any ideas? Thank you.

Upvotes: 0

Views: 18481

Answers (2)

user330315
user330315

Reputation:

This should do it:

cast(createdate as timestamp with time zone) at time zone 'UTC'

This converts the createdate to a timestamp with your current time zone (the one that is defined by your client through SESSIONTIMEZONE). It then converts that to UTC.

Upvotes: 3

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

It is not possible if you have a DATE or TIMESTAMP value, because those data types do not have any time zone information and thus it is not possible to convert to any other time zone - unless you treat the value as "local time zone".

There are several solutions:

  • createdate at time zone 'UTC'
  • SYS_EXTRACT_UTC(createdate)
  • FROM_TZ(createdate, 'UTC')

The result types are different, e.g. FROM_TZ returns as TIMESTAMP WITH TIME ZONE values, whereas SYS_EXTRACT_UTC returns a TIMESTAMP value.

Upvotes: 1

Related Questions