SQL timezone question

[msf11]

Here is my situation. I have dates for transactions that are stored in the db. These dates are in GMT (greenwich mean time) and i want to convert the dates to EST(eastern standard time) or EDT (easter daylight time) according to the date. If the date occurs in February, i want a query that will automatically convert the date to EST. If the date occurs in July, i want to automatically convert it to EDT. The problem is that i need to run a single query over large date ranges and need to good way to convert the date for each record. The NEW_TIME function will not work because i need to know what timezone (EDT or EST) i want to convert the date to.

Does anyone know of a function that takes a date and general timezone (eastern) as an input and outputs the correct timezone (EDT or EST)? Or does anyone have any other ideas? Thanks for the help!

 

[SantaMufasa]

MSF,

If I were in your position, I'd write a user-defined function [maybe called Chg_TZ() ], which would act similarly to the Oracle built-in, NEW_TIME, but would also check for date ranges to determine if "standard" or "daylight" time applied, and return the proper time-zone appellation. You could also cause your function to properly accommodate Arizona and the parts of Indiana that do not observe typical time-zone rules. Additionally, you could build accommodations for the MANY time zones worldwide that Oracle does not cover; Oracle NEW_TIME parochially accommodates only North American time zones, plus GMT.

 

[sem]

You may try to combine NEW_TIME with DECODE (or CASE). You may also create function-based index on the result expression or update your existing column.

Regards, Dima