Need Help with Date Conversion to Session Timezone
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Need Help with Date Conversion to Session Timezone

  1. #1
    Join Date
    Nov 2006
    Posts
    3

    Need Help with Date Conversion to Session Timezone

    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

  2. #2
    Join Date
    Nov 2006
    Posts
    3

    Got it...

    CREATE OR REPLACE FUNCTION TO_STZ (f_date DATE) RETURN DATE IS
    BEGIN
    RETURN f_date+(extract(TIMEZONE_HOUR from
    current_timestamp)-extract(TIMEZONE_HOUR from systimestamp))/24;
    END TO_STZ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width