The other day in our architecture group meeting the question was raised on how SQL Server handled future dates in relation to lights savings time.  For example if a play was scheduled in January for a date in July at 1:00 PM would reflect this time or 2:00 PM because of the jump in a hour for day light savings time.  This is a common issue with many applications, but not SQL Server.  It all relates to the time source the application is getting the time from.  Applications use one of two sources the CPU which works on UTC or it uses an OS api which reflects the time zone setup in the OS.  Of course you’re simply transferring the burden from one program to the next (OS), but it has its advantage to work off the OS time rather then the CPU, but it also has its disadvantages as well. 

Before going into how SQL uses its source, you need to understand how SQL maintains its date.  The “datetime” datatype is eight bytes, or 2 ints, divided between date and time.    The first int represents the date where the base date is 1900 and increments are set at one day.  The second int or four bytes is the time with a base of 12:00 AM, increments are 1/300 of a second.  If you want to see how this works run the following queries:

--Convert date to a binary

select convert(varbinary(100), getdate())

--Take the first 8 hex values and convert ensure the "0x" is prefix

select convert(int, 0x00009AC4)

--Do a date diff with 1/1/1900 The dates should be the same

select datediff(day, '1/1/1900', getdate())

Now that we know how data is stored there is the critical nature of what source does sql use.  As I said earlier applications can use the converted OS time or do the coversion itself against the cpu’s time that’s using UTC.  SQL uses both known as “High Resolution Timer” and “Low Resolution Timer”.  The High resolution timer works off the cpu timer or UTC, which SQL queries for internal processes such as workers, locks and data cleanup.  The Low resolution timer calls the windows api GetTickCount which returns the OS time.  This is used by external functions such as Getdate().   Since these external needs are based on the OS time, there unaffected by changes in day light savings time, all time is still based on delta of midnight as the OS represents the time zone. 

This type of tradeoff between High and Low resolution seems to fit most needs, unless you get into time comparisons, such as Dateadd or Datediff functions.  These are unaware  of day light savings time or UTC, there given a value,  and compute the deltas and assume no day light time.   As with SQL your application will probably require different time values based on regional time and UTC.  SQL provides this functionality in the form of the function getutcdate.