Daylight Saving Time (DST) and SQL server puzzle

  • Article

I am back after a long break. I went into shell to take some deep dive with BizTalk Server 2006, Enterprise Project Management 2007. Currently I am sharpening my SQL Server brain with some of the best brains around the world on SQL Server in one of the most the grilling training I have seen so far - SQL Ranger workshop. I will be back in full swing by month end.

In the meantime – Just thought of putting some of the information on Daylight Saving Time (DST) changes and its impact on SQL server.

Does DST have any impact! Should I worry?

Well this is not the definitive answer but on high level SQL uses GetTickCount () – typical windows API and time stamp counter of CPU for time resolution activities required for its functioning.

As jobs and background process are executed relative to specific time; they should not have any impact due to DST.

For all practical purpose SQL Server uses Windows OS capabilities for time information. So ground work should start from applying the required patches for DST for Widows OS. Refer to below:

· Update in Windows to handle the new DST in the US https://support.microsoft.com/?kbid=928388.

· Update in Windows to handle the new DST in the Australia https://support.microsoft.com/?kbid=928388.

· Have a look at - https://support.microsoft.com/default.aspx?scid=KB;[LN];924840

· New time zone update for Sri Lanka - https://support.microsoft.com/kb/918093/

Actions that you must take

If you have SQL Server installed and configured for automatic DST update, and the time zone computer follows the DST changes, you must take the following actions:

· https://support.microsoft.com/kb/924840/

· https://support.microsoft.com/kb/931815/

· Do catch up with changes on custom application where you choose to hard coded time zone handling code in applications / directly putting date value in tables without putting metadata object for time zone handling! Wake up time!!

Some the things to watch on:

o Any date based calculation

o Sections of application based on ORDER BY clause on date columns

o Output of date functions used for key value generation

o Queue based implementation based purely on date

Good resoruce for devlopers - https://msdn2.microsoft.com/en-us/vstudio/bb264729.aspx 

Refer to https://support.microsoft.com/gp/dst_topissues for complete set of products affected by DST.

Refer to master article for DST actions - Big encytic URL - https://support.microsoft.com/gp/dst_it3?systemlist=w1.w2.w3.w4.w5.w6.w7.w8.w9.w35.w10.w11.w12.w13.w14.w15.w16.w17.w30.w28.w19.w31.w20.w21.w22.w23.w24.w25.w32.w33.w34.&applist=e1.e2.e3.e4.e5.e6.e7.e8.e9.o1.o2.o3.o4.o5.sp1.sp2.sq1.sq2.wm1.ce1.ce2.ce3.ce4.

· Look at usage of GetDate(), DateAdd, or DateDiff() function in customized statements. – wondering why!!

Refer to https://support.microsoft.com/default.aspx?scid=kb;en-us;931975&sd=rss&spid=2855 for complete details.