Hi guys,
I am trying to add a timezone do a date field and then convert it to another time zone (in one select statement).
Why am I doing this:
I have a database, where all dates are UTC+0 (in summer as well as winter), but
select dbtimezone, sessiontimezone from dual;
results in:
+02:00 Europe/Berlin
which i cannot change. So in summer I Need to add +2h to the dates, and in winter +1h, to fit my actual location.
I tried adding the UTC timezone in the following variants:
FROM_TZ(CAST(MyDate AS TIMESTAMP), 'UTC' ) AS UTC_TXT
TO_TIMESTAMP_TZ(TO_CHAR(MyDate || ' +00:00'),'DD.MM.YYYY HH24:MI:SS TZH:TZM') AS UTC_NUM
and then cast it to the actual time zone, which does not work. Any ideas on how I can make it work or different approaches?
My approach:
TO_DATE(TO_CHAR((CAST( FROM_TZ(CAST(MyDate AS TIMESTAMP), 'UTC' ) AS TIMESTAMP) AT TIME ZONE DBTIMEZONE),'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS Corrected_Date
Thanks in advance