Inside Datetime Data

In "Solving the Datetime Mystery," September 2000, I began a discussion of datetime values. In this issue, I conclude that discussion by telling you how SQL Server determines which century to assume when you enter a two-digit year, and why SQL Server won't accept a date before 1753. Also, I tell you about one more internal storage detail and show you code for searching for various datetime values in your tables.

When SQL Server stores a date, the program leaves no ambiguity about the year. The date is stored as the number of days from a base date of January 1, 1900, so a date in the 1900s would be a smaller value than a date in the 2000s, and a date in the 1800s would be a negative number. But what happens when a user or an application enters a character string with a two-digit year? How does SQL Server figure out which year is intended? SQL Server's default behavior is to interpret a two-digit year as 19yy if the value is greater than or equal to 50 and as 20yy if the value is less than 50. To verify this behavior, I used Query Analyzer to execute these two statements:

SELECT convert(datetime, '1/1/49')
SELECT convert(datetime, '1/1/50')

I got the following results:

2049-01-01 00:00:00.000
1950-01-01 00:00:00.000

SQL Server interpreted the two-digit 49 as 2049 and the two-digit 50 as 1950. That interpretation works now, but by the year 2051, you won't want SQL Server to interpret a two-digit year of 51 as 1951. With SQL Server 7.0, you can change the cutoff year that determines how SQL Server interprets a two-digit year. A two-digit year that is less than or equal to the last two digits of the cutoff year will have the same first two digits as the cutoff year. And for a two-digit year that is greater than the last two digits of the cutoff year, SQL Server interprets the first two digits as a number that is one less than the cutoff year's first two digits. To change the cutoff year, select the Server Properties tab in the Properties dialog box in the SQL Server Enterprise Manager. Or you can use the sp_configure stored procedure:

EXEC sp_configure 'two digit year cutoff', '2000'
RECONFIGURE

In this example, because the two-digit year cutoff is 2000, SQL Server interprets all two-digit years except 00 as occurring in the 1900s. When the default two-digit year cutoff value is 2049, SQL Server interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950.

Be aware that although SQL Server uses 2049 as the cutoff year for interpreting dates, OLE Automation objects use 2030. You can use the two-digit year cutoff option to provide consistency in date values between SQL Server and client applications. However, I strongly recommend that you always use four-digit years in your data to avoid ambiguity in your applications.

Limits of Date Values
SQL Server Books Online (BOL) defines the ranges of the two datetime data types this way: "Use datetime to store dates in the range from January 1, 1753, through December 31, 9999. Use smalldatetime to store dates in the range from January 1, 1900, through June 6, 2079." Two of these dates seem arbitrary. Why is the earliest datetime value in 1753? And why is the last smalldatetime value not at the end of a century, or even at the end of a month? I mentioned in my last column that datetime values use 4 bytes to store the number of days before or after the base date. Smalldatetime values use only 2 bytes. Generally, 2 bytes can hold numbers between -2¹⁵ and +2¹⁵ - 1, using 15 of the 16 bits for the data and one bit for the sign. If you ignore the sign and use that 16th bit for data, the range of possible values more than doubles. Instead of a maximum 2-byte value of 32,767, the value is 65,535. And 65,535 days after the base date of January 1, 1900, is June 6, 2079.

The reason for the early cutoff of possible datetime values isn't mathematical but historical. In September 1752, Great Britain adopted the Gregorian calendar, which differed from the one previously used in Great Britain and its colonies by 12 days. The change happened on September 2, 1752, so the next day was September 14, 1752.

So, with 12 days lost, how can you compute dates? For example, how can you compute the number of days between October 12, 1492, and July 4, 1776? Do you include those missing 12 days? To avoid having to solve this problem, the original Sybase SQL Server developers decided not to allow dates before 1753. You can store earlier dates by using character fields, but you can't use any datetime functions with the earlier dates that you store in character fields.

Datetime and smalldatetime values also differ in the precision of the time information they can store. Last month, I mentioned that datetime records time to the nearest tick, which is 3.3 milliseconds (ms). Smalldatetime stores time only to the nearest minute. But what happens when you try to convert a datetime value to smalldatetime? You might have read or assumed that SQL Server rounds smalldatetime values to the nearest minute, and the following queries seem to bear this idea out. For example, the query

SELECT CAST('2000-07-08 14:55:29' AS smalldatetime)
returns the time as 14:55, whereas
SELECT CAST('2000-07-08 14:55:30' AS smalldatetime)

returns the time as 14:56. However, times aren't always rounded to the nearest minute because SQL Server rounds all datetime values to the nearest .000, .003, or .007 seconds; therefore, 29.999 rounds up to 30.000 before being converted to smalldatetime, and 29.998 rounds down to 29.997. Thus, the query

SELECT CAST('2000-07-08 14:55:29.998' AS smalldatetime)

returns time as 14:55, whereas

SELECT CAST('2000-07-08 14:55:29.999' AS smalldatetime)

returns the time as 14:56.