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

Solution 1:

First let's dissolve your expression

FROM_TZ(CAST(TO_DATE(TO_CHAR(q.created_date, 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS') ASTIMESTAMP), 'Europe/London') ATTIME 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') ATTIME 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 ATTIME ZONE 'America/New_York', 'DD-MON-YYYY HH24:MI:SS')

Solution 2:

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') astimestamp), 'Europe/London') , 1, 20) attime zone 'America/New_York'elsenullendas Message_Rcd_Date_Time;

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