jason Palmer jason Palmer - 8 months ago 50
SQL Question

date/time Conversion between different timezones

I am using the following sql to covert a date/time value from one timezone to another

from_tz(cast(to_date(to_char(q.created_date, 'DDMMYYYY:HH24:MI:SS'),
'DDMMYYYY:HH24:MI:SS') as timestamp), 'Europe/London') at time zone 'America/New_York'
else null end as Message_Rcd_Date_Time

output from the above is as follows:

29-OCT-2016 14:28:16.000000 -04:00

What I want to do is output this date/time as shown below, excluding the timezone, can you please tell me what I need to change in order to achieve this?

29-OCT-2016 14:28:16

Answer Source

First let's dissolve your expression

FROM_TZ(CAST(TO_DATE(TO_CHAR(q.created_date, 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS') AS TIMESTAMP), 'Europe/London') AT TIME ZONE 'America/New_York'

does following:

  1. TO_CHAR(q.created_date, 'DDMMYYYY:HH24:MI:SS') -> Convert created_date value to VARCHAR2
  2. TO_DATE(..., 'DDMMYYYY:HH24:MI:SS') -> Convert it back to a DATE
  3. CAST(... AS TIMESTAMP) -> Convert it to a TIMESTAMP (without time zone)
  4. FROM_TZ(..., 'Europe/London') -> Attach time zone 'Europe/London' to it
  5. ... AT TIME ZONE 'America/New_York' -> Convert to time zone 'America/New_York'

Point 1,2 and 3 are useless! Since created_date is a TIMESTAMP you can do it shorter

TO_CHAR(FROM_TZ(q.created_date, 'Europe/London') AT TIME ZONE 'America/New_York', 'DD-MON-YYYY HH24:MI:SS')

In case your SESSIONTIMEZONE is Europe/London you can even make

TO_CHAR(q.created_date AT TIME ZONE 'America/New_York', 'DD-MON-YYYY HH24:MI:SS')