Applications using historical data from 2006 (or any previous years) can have invalid results

Executive Summary:

Applications using historical data from 2006 (or any previous years) can have invalid results. Microsoft is currently working to provide support for vendors and software developers for their applications, if the application does not provide historical DST start and end-date tables. The change in DST start-time was something applications on top of Windows could address, or not, by maintaining a historical DST-Change date to do this conversion (for countries like Israel). However, this had not been an issue in the USA previously, as DST had always started on the same day, prior to 2007. Windows provides the functionality to allow the operating system to address this, starting in Vista.

 

Example:

If a financial application accesses financial data from previous years which are stored within SQL, that application’s results can be incorrect. The application’s query results (or stored procedures) may fail or return invalid dates for periods before 2007 (any year with the older DST-start/end dates). For explanation on DST’s affect on time stamps in Office, see this article:

office.microsoft.com/en-us/help/HA102201961033.aspx

 

Detail

SQL Server DST and DateTime

With details below to expand on the following, it is easiest to summarize by saying that SQL server stores datetime ignorant of DST. Key points to consider are that:

· SQL Server does not manipulate datetime.

· The datetime value is stored as is with no modification on the part of SQL Server during insert/update/select.

· When running applications that appear to have results that have a manipulated the date, investigate the business logic in the stored procedures/selects/middleware/client applications.

· Also keep in mind that SQL is unaware of its physical or time zone location, with a base of users that could be global and in dozens of time zones SQL will accept any valid time remaining unaffected by DST rules of the host server or accessing client.

· SQL Server does not store datetime as an EPOCH/POSIX style.

 

Why SQL does not alter Datetime

The basic rules of relation databases ACID;

The one property of ACID (Atomicity, Consistency, Isolation, Durability) is Durability. If SQL manipulates the data on the way in/out of the database it is a violation of standards of a stable RDMS. So if SQL changes your date, it would not be Durable and would violate the standards of a stable RDMS. The version of standards for ACID can be viewed in ISO/IEC 10026-1:1992 Section 4; and MS SQL Server follows this standard. So for SQL to violate that standard would be a significant departure from ISO standards. Business logic contained within the application, or stored procedures, middle ware, or an additional application layer function may choose to maintain a change table for DST dates, without violating the ISO standards. This holds true for any RDMS, that wants to comply with standards set forth in ISO/IEC.

Ref: <en.wikipedia.org/wiki/ACID>

 

In relation to SQL Datetime compared to EPOCH/POSIX time on UNIX;

SQL DateTime

Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.

Ref: <msdn2.microsoft.com/en-us/library/ms187819.aspx>

 

UNIX EPOCH/POSIX time

Whereas EPOCH/POSIX time is based on number of seconds since midnight January 1, 1970, not counting leap seconds stored as an integer value. This is NOT a true encoding of UTC since UTC does account for leap seconds.

Ref: <en.wikipedia.org/wiki/Unix_time>

 

GETDATE() Function

The GETDATE() function requests the date and time from the local Host Operating System regardless of time zone and presents the server time to the calling T-SQL. No time or date manipulation is done on the request.

REF: <msdn2.microsoft.com/en-us/library/ms188383.aspx>

 

 

GETUTCDATE()

The GETUTCDATE() returns the datetime value that represents Coordinated Universal Time or GMT. The current UTC is derived from the current local time and time zone data (or bias) on the SQL Server host computer. GETUTCDATE() should not be confused with “EPOCH UTC” time, they are not the same, see the link below for details.

Ref: <msdn2.microsoft.com/en-us/library/ms178635.aspx>

 

DATEDIFF() & DATEADD() Functions

               These two SQL datetime functions are not timezone or DST aware. When application uses these datetime functions to manipulate datetime data, application developers need to

               make sure correct timezone and DST are applied before saving the result to the database.

               Ref: support.microsoft.com/?id=931975, "How to prepare SQL Server 2005 and SQL Server 2000 for changes to daylight saving time in 2007"