SQL Server Magazine

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.

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 datetime
select @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 YDM
declare @today datetime
select @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.

   Prev. page   [1] 2     next page