SQL Server and daylight savings changes

In case you don't already know, Daylight Savings Time starts three weeks earlier and ends four weeks later this year than in previous years.  This does not affect SQL Server directly because it uses the Windows clock as its clock, but you might have some stored procedures, views, or functions that try to calculate DST based on the old dates.  If you think you might, try running the following on each of your databases.  Feel free to change the "LIKE" arguments or add "OR" clauses.

-- procedures
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE='PROCEDURE'
 AND (ROUTINE_DEFINITION LIKE '%dst%'
 OR ROUTINE_DEFINITION LIKE '%daylight%'
 OR ROUTINE_DEFINITION LIKE '%first%sunday%'
 OR ROUTINE_DEFINITION LIKE '%last%sunday%')
-- functions
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE='FUNCTION'
 AND (ROUTINE_DEFINITION LIKE '%dst%'
 OR ROUTINE_DEFINITION LIKE '%daylight%'
 OR ROUTINE_DEFINITION LIKE '%first%sunday%'
 OR ROUTINE_DEFINITION LIKE '%last%sunday%')
-- views
 SELECT table_name, view_definition
     FROM INFORMATION_SCHEMA.VIEWS
     WHERE VIEW_DEFINITION LIKE '%dst%'
 OR VIEW_DEFINITION LIKE '%daylight%'
 OR VIEW_DEFINITION LIKE '%first%sunday%'
 OR VIEW_DEFINITION LIKE '%last%sunday%'

 

Information_Schema views: http://msdn2.microsoft.com/en-us/library/ms186778.aspx

Preparing for Daylight Savings: http://www.microsoft.com/windows/timezone/dst2007.mspx

Print | posted on Tuesday, January 30, 2007 8:53 PM

Comments on this post

# re: SQL Server and daylight savings changes

Requesting Gravatar...
Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx
Left by Paul Skobel on Feb 16, 2007 2:18 PM

Your comment:

 (will show your gravatar)
 
Please add 1 and 3 and type the answer here: