Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT CHANGEOVER_DATE
- FROM SOME_SYSTEM_TABLE
- WHERE DATE_TYPE = 'DAYLIGHT_SAVINGS_CHANGEOVER'
- AND TO_CHAR(CHANGEOVER_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY'); -- in the current year
- Function DaylightSavingTimeStart (p_Date IN Date)
- Return Date Is
- v_Date Date;
- v_LoopIndex Integer;
- Begin
- --Set the date to the 8th day of March which will effectively skip the first Sunday.
- v_Date := to_date('03/08/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
- --Advance to the second Sunday.
- FOR v_LoopIndex IN 0..6 LOOP
- If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
- Return v_Date + v_LoopIndex;
- End If;
- END LOOP;
- End;
- Function DaylightSavingTimeEnd (p_Date IN Date)
- Return Date Is
- v_Date Date;
- v_LoopIndex Integer;
- Begin
- --Set Date to the first of November this year
- v_Date := to_date('11/01/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
- --Advance to the first Sunday
- FOR v_LoopIndex IN 0..6 LOOP
- If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
- Return v_Date + v_LoopIndex;
- End If;
- END LOOP;
- End;
- ALTER SESSION SET time_zone='America/Phoenix';
- DROP TABLE TimeDifferences;
- CREATE TABLE TimeDifferences(LocalTimeZone TIMESTAMP(0) WITH LOCAL TIME ZONE);
- INSERT INTO TimeDifferences
- (
- SELECT to_date('01/01/' || to_char(sysdate-365,'YYYY') || '12:00:00','MM/DD/YYYYHH24:MI:SS')+rownum-1
- FROM dual CONNECT BY rownum<=365
- );
- COMMIT;
- ALTER SESSION SET time_zone='America/Edmonton';
- SELECT LocalTimeZone-1 DaylightSavingTimeStartAndEnd
- FROM
- (
- SELECT LocalTimeZone,
- to_char(LocalTimeZone,'HH24') Hour1,
- LEAD(to_char(LocalTimeZone,'HH24')) OVER (ORDER BY LocalTimeZone) Hour2
- FROM TimeDifferences
- )
- WHERE Hour1 <> Hour2;
Add Comment
Please, Sign In to add comment