The Wrecking Bawl

Destructuring query language, one keyword at a time.


News


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

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Comments

Gravatar # re: SQL Server and daylight savings changes
Posted by Paul Skobel on 2/16/2007 2:18 PM
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

Post A Comment
Title:
Name:
Email:
Website:
Comment:
Verification: