Help with Date Conversion to Session Timezone

From: MartyL <martyrl_at_yahoo.com>
Date: Wed, 1 Nov 2006 07:15:30 -0800
Message-ID: <J_ednTWVz9MqXdXYnZ2dnUVZ_q6dnZ2d_at_comcast.com>

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 in the table to the timezone of the user.

I initially wrote a function, TO_STZ to convert a date to the sessiontimezone:

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.

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)

Any suggestions on a function that does what I want?

Thanks, Marty Received on Wed Nov 01 2006 - 16:15:30 CET