Geeks With Blogs

News
The Wrecking Bawl Destructuring query language, one keyword at a time.

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

Posted on Tuesday, January 30, 2007 8:53 PM all tech stuff , SQL Server | Back to top


Comments on this post: SQL Server and daylight savings changes

# 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)


Copyright © Alex Bransky | Powered by: GeeksWithBlogs.net