Menu
I have a field with a date/time value like this:
2009-11-17 18:40:05
It's in UTC. In the query how can I convert this to EST?
I'm trying something like this but it throws an error.
// datetime is the field name
SELECT
FROM_TZ(TIMESTAMP TO_DATE(datetime, 'yyyy-mm-dd hh24miss'), 'EST') AS DT
FROM
db_name
703
SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETUTCDATE()), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS LOCAL_IST; Here, the GETUTCDATE() function can be used to get the current date and time UTC. Using this query the UTC gets converted to local IST.
Here's an example of setting TIME_ZONE to such values: ALTER SESSION SET TIME_ZONE=local; ALTER SESSION SET TIME_ZONE=dbtimezone; ALTER SESSION SET TIME_ZONE='Canada/Eastern'; ALTER SESSION SET TIME_ZONE='-04:00'; You can check your current session's time zone with the SESSIONTIMEZONE function.
Introduction to Oracle TIMESTAMP WITH TIME ZONE Its default value is 6. Oracle considers two TIMESTAMP WITH TIME ZONE values are equal if they represent the same value in UTC regardless of the time zone data.
I had to tweak it slightly to get it to work on my database, but this worked:
select from_tz(to_timestamp('2009-11-17 18:40:05','yyyy-mm-dd hh24:mi:ss'), 'UTC')
at time zone 'America/New_York' from dual
The key is the "at time zone" syntax.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
