Solving the Datetime Mystery

One of the most interesting and confusing data types that SQL Server supports is datetime. I see more questions in online public discussion forums about this type than any other. I thought that I knew almost everything worth knowing about datetime data, and I've answered many questions in the discussion forums. But during the past several weeks, I've discovered that some things I thought were true about datetime aren't true at all. In this article, I shed light on some very confusing issues regarding this misunderstood data type.

Related: Converting Data Types

Related: Help for the DATETIME data type and DST on SQL Server

First, the most important characteristic of datetime data is that every datetime value (whether it's a column in a table, a local variable, or the return value of a function) contains both a date and a time. To illustrate this fact, I can create two datetime variables, then set one to a date and the other to a time. Listing 1 shows the code to create these variables. I received the output that Figure 1 shows when I used Query Analyzer to execute the batch in Listing 1.

Because the two variables were datetime types, they needed to include both date and time information. You can see that for a date with no time specified, SQL Server assumed a time of midnight (00:00:00.000), and for a time with no date specified, it assumed a date of January 1, 1900.

Many people believe that the format in Figure 1 is the format that SQL Server uses internally to store the datetime information. This assumption couldn't be further from the truth. SQL Server stores datetime data in a special format that you never see, and the client tool you use determines what the datetime value looks like. For example, the Query Analyzer is an ODBC-based tool, as is the osql command-line tool. Both tools display datetime in ODBC-Canonical datetime format, as the output in Figure 1 shows. However, if you run the same batch in the isql command-line tool, which is DB-Library based, the output (which Figure 2 shows) is different. This output is from the same SQL Server system, which performs the same operations and passes the same information as the example that yielded the output in Figure 1. The isql command-line tool knows that the data is datetime, but it needs to use ASCII characters to display the data, so the tool determines the display format.

Both of these tool-determined formats are different from the format that SQL Server uses when converting a datetime value to a character string. You can use the system function getdate(), which returns the current date and time as a datetime value, and have SQL Server convert that value to a character string before passing it to the client:

SELECT today = convert(char(20), getdate( ) )

I got the following output:

today        ----------------------Jun  8 2000  9:45PM

Note that when SQL Server uses the convert function to convert the datetime information into a character string, it sends a character string to the client tool. The client tool has no knowledge that this string formerly was datetime information. The tool receives only character information and displays the characters as such. SQL Server's format is only slightly different from the isql tool's format. You need to look closely to see that SQL Server allows two characters for the day of the month. For example, if the date is June 8, which means that the day requires only one character, then SQL Server inserts two spaces between the month and the day. This detail might seem insignificant, but it becomes important in certain operations.

So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when I didn't supply the date in my first example. SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.

SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes. The code in Figure 3 then converts each set of 4 bytes into an integer.

Now that you know that the tool determines the display format for a datetime value, what can you do if you want a different format from what the tool would display? The format that the previous example shows is the default that SQL Server uses when converting datetime data to character string. When you use convert( ) to change a datetime type into a character string type, you can supply a third argument called a style. SQL Server Books Online (BOL) thoroughly documents the style value options; look in the section titled "Cast and Convert." But to get you started, I'll show you a few examples. Figure 4 contains three select statements and their results. Note that all styles less than 100 return the year in two digits, and all styles greater than 100 return the year in four digits. The exceptions are styles 8 and 108, which don't return the year at all; these styles return only the time. Most of the other formats return only the date portion of the datetime data.

SQL Server's internal storage format for datetime data is unambiguous. Basing the format on the number of days since January 1, 1900, prevents confusing the month with the day or mistaking which century you're referring to. However, if you try to insert a character string representing a date into a SQL Server datetime field or variable, confusion can result. For example, suppose I issue the following code:

declare @today datetimeselect @today = '3/4/48'select @today

What datetime value will SQL Server return? Does 3/4/48 mean March 4 or April 3? Is the year 1948 or 2048, or some other year, such as 48 CE? First, your default language controls the day and month order, and for US English, the format is Month-Day-Year. I use a US English SQL Server machine; so if I tried to assign the string '30/4/48' to the datetime variable, I would receive an error message because 30 isn't a valid month. However, I can override the default datetime format for my language by using the set option SET DATEFORMAT. You can choose from six formats, which are the six possible arrangements of the letters Y, M, and D: MDY, DMY, DYM, MYD, YDM, and YMD. Like all SET options, the value supplied to SET DATEFORMAT is valid for only one connection. Here's an example:

set DATEFORMAT YDMdeclare @today datetimeselect @today = '3/4/8'select @today

The output is the string 2003-08-04 00:00:00.000, with 3 interpreted as the year 2003, 4 interpreted as the day 04, and 8 interpreted as the month 08.

Setting the DATEFORMAT for each connection can be cumbersome if all your data comes from the same source in the same format. To streamline the process, you can change your default language. To define a new language, you need only the names for the days of the week, the names of the months, and a default date format.

The stored procedure sp_ helplanguage contains several language definitions with only these elements. If you want to use the English names of months and days, but use a default input format of DMY instead of MDY, you can change the language used by any login to the British format. You can use sp_defaultlanguage to make this change:

sp_defaultlanguage sue, british

Now, whenever user Sue logs in, SQL Server will interpret dates in her requests as DMY (day-month-year). If she executes the command

SELECT convert(datetime, '3/4/48')

she will get the result

2048-04-03 00:00:00.000

Special Formats

Your default language or DATEFORMAT setting never affects the ISO standard format. SQL Server will always interpret the ISO standard format, with all numbers and no punctuation, as YMD. So regardless of whether Sue (with her British default format) or I (with my US format) enter the command

select convert(datetime, '20001012')

we'll both get a date that specifies October 12, 2000. Make sure that you always put dates in single quotes. SQL Server applies your DATEFORMAT setting only when it converts character strings to datetime values. If you omit the quotes in the example above, SQL Server will assume that you meant the number 20,001,012 and will interpret that figure as the number of days after the base date of January 1, 1900. The result would be outside the range of possible datetime values that SQL Server can store.

One other date format can override your default language or DATEFORMAT setting, but this format behaves somewhat inconsistently. If you enter a date in all numeric format with the year first but you include punctuation (as in 1999.05.02), SQL Server will assume that the first part is the year, even if your DATEFORMAT is DMY. How does SQL Server determine which number is the month and which is the day? SQL Server will still use your DATEFORMAT setting to determine the order of the month and the date values. So, if your DATEFORMAT is MDY, the following statement

select convert(datetime, '1999.5.2')

will return

1999-05-02 00:00:00.000

If your DATEFORMAT is DMY, then

convert(datetime, '1999.5.2')

will return

1999-02-05 00:00:00.000

and

convert(datetime, '1999.5.22')

will return an out-of-range error. This behavior seems quite inconsistent to me. SQL Server partially ignores the DATEFORMAT, but not completely. I suggest that you avoid this format for inputting dates, and omit the punctuation if you use all numbers.

Next month, I'll continue to look at the input format. Also, I'll tell you how SQL Server determines which century you mean when you enter a two-digit year, why you can't have a date before 1753, and why SQL Server seems to produce strange rounding errors when converting data from datetime to smalldatetime types. I'll also show you some coding tricks for searching for various datetime values in your tables. So, do we have a date?