1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle call to identify whether dst is applied to time?

Discussion in 'SQL PL/SQL' started by Schnitzel, Apr 26, 2019.

  1. Schnitzel

    Schnitzel Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Winnipeg, MB
    Is there an Oracle PL/SQL call I can issue to identify whether the Oracle time on our server is in Daylight Savings Time (DST)?

    I have the following PL/SQL function that is intended to calculate the UTC date/time equivalent of a passed date/time argument value, expressed in the local date/time:
    Code (SQL):
    FUNCTION GetUTCDateTimeISOFormat(pdteDate IN DATE:= SYSDATE)
      RETURN VARCHAR
    IS
    BEGIN
      RETURN TO_CHAR(SYS_EXTRACT_UTC(CAST(pdteDate AS TIMESTAMP)), 'yyyy-mm-dd"T"HH24:MI:SS');
    EXCEPTION
      WHEN OTHERS THEN
         raise_application_error(-20005, SQLERRM);
    END GetUTCDateTimeISOFormat;
    If I pass a date/time value of 31-Jan-2019 12am (NO DST in effect), I get a return value of 5 am on 31-Jan-2019. If I pass in 31-Jul-2019 12am (DST IS in effect), I still get 5am on that date.

    So SYS_EXTRACT_UTC makes no distinction between DST and no DST in the passed in argument value.

    Is there an Oracle call I can use to identify whether the Oracle time is in DST (that returns either True or False)? Using that return value, I can then change the above code to deduct one hour from the input before passing it to the SYS_EXTRACT_UTC function, should DST be in effect.

    Thanks for any help you can give.

    Wayne