CODEWITHSUNDEEP
sqloracle-databaseoracle11g
CrazyCoder
CrazyCoder

Reputation: 780

ORA-01857: not a valid time zone

I am trying to convert from UTC time zone to GMT time zone.

I ran this below query and getting ORA error. 

select NEW_TIME(SYSDATE, 'UTC', 'GMT') from dual;

And error is

Error starting at line : 1 in command -
select NEW_TIME(SYSDATE, 'UTC', 'GMT') from dual
Error report -
ORA-01857: not a valid time zone

I googled and find that NEW_TIME function is not accepting UTC time zone.

So, Can you please suggest me alternate solution/any way to convert from UTC to GMT?

Upvotes: 0

Views: 1891

Answers (2)

MT0
MT0

Reputation: 168001

Use FROM_TZ from convert a timestamp without a time zone to a timestamp with time zone (i.e. UTC) and then use AT TIME ZONE 'GMT' to convert it from the first time zone to the GMT time zone. You'll need to use CAST in various places as FROM_TZ expects a TIMESTAMP rather than a DATE and then you need to cast back to a DATE at the end (assuming you don't want a TIMESTAMP value):

SELECT CAST(
         FROM_TZ(
           CAST( SYSDATE AS TIMESTAMP ),
           'UTC'
         )
         AT TIME ZONE 'GMT'
         AS DATE
       ) As gmt_time
FROM DUAL

Output:

| GMT_TIME            |
| :------------------ |
| 2019-04-10T14:05:37 |

db<>fiddle here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521399

UTC is also known as GMT, the latter which NEW_TIME already accepts. So, what you are trying to is equivalent to:

SELECT NEW_TIME(SYSDATE, 'GMT', 'GMT')
FROM dual;

The call to NEW_TIME doesn't make any sense of course. Check here for a list of accepted timezone codes.

Upvotes: 1

Related Questions