date/time Conversion between different timezones

Question

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 1

Not sure about Oracle but below query can help.

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

If it would have been SQL Server only LEFT function would have worked.

Answer 2

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')