I have an application that is used by users across the country. It reads from a table in a centrally located database. It needs to convert a standard date type field in the table to the timezone of the user.

I initially wrote a function, TO_STZ to convert a date to the sessiontimezone that essentially takes the difference between dbtimezone and sessiontimezone and adjust the date by the difference:

RETURN arg_date(+(to_number(substr(dbtimezone,2,2))-to_number(substr(sessiontimezone,2,2)/24

When the user issues the statement "SELECT TO_STZ(date_field) from TABLE;" the date in the table was converted as desired.

This worked fine up until Daylight Saving Time - at which point sessiontime changed by -1 hour but dbtimezone stayed the same (I didn't realize at the time that it was a constant offset from GMT)

I could ask my dba to change the dbtimezone - but this doesn't seem right & he would have to do it 2x a year.

Any suggestions on a function that does what I want?

Thanks, Marty