Understanding date/time data types in SQL Server can be difficult, especially when you add TIMESTAMP into the mix....
In part one of this series on date/time, you'll learn the basics of how data is stored within DATETIME and SMALLDATETIME and get an overview of the TIMESTAMP data type -- as it's often confused with the two primary date/time data types.
Working with date/time values in SQL Server 2005 can sometimes be confusing. Because date/time data types store both date and time values, manipulating those values is not always a straightforward process, or so it seems.
A basic understanding of these data types is essential when working with this sort of data. In this article, I describe the two basic datetime data types in SQL Server -- DATETIME and SMALLDATETIME -- and how data is stored within each type. In addition, I provide an overview of the TIMESTAMP data type so you understand how it differs from the two date/time data types.
SQL Server DATETIME data type
No doubt, the DATETIME data type will be the most valuable to your applications. Values in a DATETIME column (or in a variable) are stored as two four-byte integers. The first integer represents the date, and the second integer represents the time.
We calculate the date integer against the base date January 1, 1900. The integer represents the number of days before or after that date. As a result, the DATETIME data type supports only dates that are represented by the integers supported by the four-byte range. This means that a date in a DATETIME column must fall within the range of January 1, 1753, through December 31, 9999.
The second integer in a DATETIME value, the time integer, stores the number of 1/300-second units after midnight. This means that the time is stored in milliseconds and is accurate to 3.33 milliseconds. Consequently, when you insert a value into a DATETIME column, SQL Server rounds the time to .000, .003 or .007 seconds. The following table shows several examples of how SQL Server rounds off a time value:
|Time example||Rounded to:|
The times in this table are listed by hour, then minute, then second, with the seconds rounded out to the appropriate millisecond.
As you can see, together the two integers in a DATETIME column represent a specific day at a specific time. When you retrieve a value from a DATETIME column, it displays the date and time as a series of numerical values. For example, the following Transact-SQL statement retrieves data from the PostTime column in the DatabaseLog table, which is part of the AdventureWorks sample database:
SELECT PostTime FROM dbo.DatabaseLog
WHERE DatabaseLogID = 1
The PostTime column is configured with the DATETIME data type. When you retrieve the value in SQL Server Management Studio, the value, by default, is returned in the following format:
(1 row(s) affected)
Note that the date is first displayed by year (2005), then month (10), then day (14). The date is then followed by the time, which is 1 hour, 58 minutes and 27.567 seconds. The value as a whole, then, refers to the date and time of October 14, 2005, at about 1:58 in the morning.
SQL Server SMALLDATETIME data type
The SMALLDATETIME data type is similar to DATETIME, but limited in scope. A SMALLDATETIME value is stored as two two-byte integers. The first integer represents the date, and the second integer represents the time.
As with the DATETIME data type, the SMALLDATETIME date integer is calculated against the base date January 1, 1900. However, the integer represents only the number of days after the base date, not the number before the date. This means that a date in a SMALLDATETIME column must fall within the range of January 1, 1900, through June 6, 2079.
The second integer in a SMALLDATETIME value -- the time integer -- stores the number of minutes after midnight. The time does not reflect the number of seconds, and if seconds are included in the value, they are rounded as follows:
- Values of 29.998 seconds or less are rounded down to the nearest minute.
- Values of 29.999 seconds or more are rounded up to the nearest minute.
The following table shows several examples of how SQL Server rounds off the time value:
|Time example||Rounded to:|
The original times in this table are listed by hour, then minute, then seconds, and finally milliseconds. However, as you can see, the values are rounded off to the hour and minute, but not seconds. When you retrieve a SMALLDATETIME value, you'll actually see figures that represent the seconds, but not the milliseconds, and these figures are always shown as 00. For example, the following SELECT statement uses the CAST function to convert a DATETIME value to a SMALLDATETIME value:
SELECT CAST(PostTime AS SMALLDATETIME) AS [Date/Time]
WHERE DatabaseLogID = 1
Like the earlier example, the statement retrieves data from the PostTime column in the DatabaseLog table. Only this time, the statement returns slightly different results:
(1 row(s) affected)
As you can see, the time is rounded down to 01:58. Although the two figures that represent the seconds are included, they're always displayed as 00. (Note that the CAST function, like the CONVERT function, is a T-SQL function that explicitly converts a value from one date type to another. I'll be discussing the CAST and CONVERT functions in a later article in this series. You can also find information about both functions in Microsoft SQL Server Books Online.
SQL Server TIMESTAMP data type
One other data type is important to mention -- TIMESTAMP. TIMESTAMP is very different from DATETIME and SMALLDATETIME. To begin with, it has little to do with dates or times. However, it has everything to do with row versioning. I mention it in this article only because it is often confused with the actual date/time data types.
SQL Server related topics
- SQL and SQL Server Tutorial and Reference Guide
- 'Out-of-range datetime value' error when working with dates
- Ask a question on our IT Knowledge Exchange
A TIMESTAMP data type column automatically generates binary values that provide a version stamp for every row in a table. Each time you insert a row, a version stamp is inserted into the TIMESTAMP column. Each time you update the row, the TIMESTAMP value is updated. As a result, a TIMESTAMP column provides a handy way to determine whether a row has been recently modified -- a valuable feature when you're developing an application that supports concurrent users. For example, you can use the TIMESTAMP value to determine whether to commit a transaction or roll back a transaction by comparing the original TIMESTAMP value to the most recent value. If the values are the same, you can commit the transaction. Otherwise you should roll back the transaction because you'll know that another user has modified the row.
The TIMESTAMP value uses a database counter that is incremented each time a row is inserted or updated. Because TIMESTAMP is a binary value, a row that contains a TIMESTAMP column will automatically store a value such as the following:
If the row is updated, that value will change.
When working with TIMESTAMP columns, consider these points:
- The data type is an incrementing number and does not preserve date/time data.
- The data type should not be used for candidate keys such as a primary key.
- A table can include only one TIMESTAMP column.
- The main purpose of the data type is to support row versioning. In fact, ROWVERSION is the synonym for the TIMESTAMP data type.
As you can see, a TIMESTAMP column has a very limited scope. Except perhaps for using the TIMESTAMP date type to support row versioning, you should use the DATETIME and SMALLDATETIME data types in all other cases, including when you want to record the actual times a data modification has taken place. Again, I mention the TIMESTAMP date type here only to clearly differentiate it from the actual time/date data types.
In future articles, I'll go into more depth about working with DATETIME and SMALLDATETIME values. You'll learn how to convert date/time values, retrieve specific information from those values and calculate the differences between values. I'll even be discussing the new date/time data types in SQL Server 2008.
Tip Series: Working with date/time data types in SQL Server
- Part 1: Basics for working with DATETIME and SMALLDATETIME
- Part 2: Data conversions from date/time values to character types
- Part 3: Using datetime functions GETDATE, DATENAME and DATEPART
- Part 4: Using DATEADD and DATEDIFF to calculate datetime values
- Part 5: New datetime data types in SQL Server 2008
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.