By: Tim Ford | Comments (9) | Related: > Dates
Problem
I have a column in one of my Microsoft SQL Server 2005 databases that store date and time data relating to date of birth. I'm only interested in the date of birth, not time of day, yet the data in this field stores both date and time. There are only two options for storing this data: either datetime or smalldatetime. No matter which one I use I end up with time information when I query the database table. I've heard that SQL Server 2008 offers more options for storing date and time values. Will any of these fit my needs?
Solution
Until Microsoft SQL Server 2008, we were limited in our storage and handling of date and time data. Our options consisted of either the datetime or the smalldatetime data type as you expressed. Both options were a combined field storing both date and time data. As a result we had to deal with cumbersome CONVERT() functions or involved T/SQL manipulations to massage and cajole data values stored in our databases into formats that were relevant and proper for our needs. The release of Microsoft SQL Server 2008 brought with it 4 new date and time data types: date, datetime2, datetimeoffset, and time. Each one of these will provide additional functionality and should significantly reduce the amount of coding overhead associated with managing date and time data. In this tip I'll be using the following table to demonstrate the differences between date and time data types in SQL 2008:
CREATE TABLE dbo.TempusFugit ( col_datetime datetime NULL, col_smalldatetime smalldatetime NULL, col_date date NULL, col_datetime2 datetime2(7) NULL, col_datetimeoffset datetimeoffset(7) NULL, col_time time(7) NULL ) ON [PRIMARY] GO
Our test data will be extremely simple, I'll only be inserting the current system date and time into each column in the table will provide us with varying results via the use of the GETDATE() system function.
INSERT INTO dbo.TempusFugit (col_datetime, col_smalldatetime, col_date, col_datetime2, col_datetimeoffset, col_time) VALUES (GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE()) GO
After running this INSERT statement I'll go through the columns individually to examine their associated default formatting. Additionally, I'll discuss storage requirements, and review (if applicable) what is necessary to provide the same results when using earlier versions of Microsoft SQL Server. The GETDATE() function returns the value for the current system date and time as a datetime data type. Therefore for this tip I'll be using the col_datetime column as the representative of the GETDATE() function's results in our comparisons.
SQL 2005 Data Types
The first two columns in this table correspond to existing data types that you'll recognize from SQL Server 2005 and earlier versions. These data types are still supported in SQL Server 2008: datetime and smalldatetime. Executing this SELECT query will provide us with a glimpse of those two columns.
SELECT col_datetime, col_smalldatetime FROM dbo.tempusfugit GO
The datetime and smalldatetime data types are identical with the exception of their behavior in respect to fractional seconds. The datetime data type presents fractional seconds to the precision of three, whereas smalldatetime will round up or down to the nearest second. Both data types are presented in 24-hour format. The inclusion of fractional seconds comes at a price for the datetime data type however. That price is an additional 4 bytes of storage. The smalldatetime data type is presented as YYYY-MM-DD HH:MM:SS and requires 4 bytes of storage. The datetime data type, the most-commonly used of the date-oriented types, is formatted as YYYY-MM-DD HH:MM:SS[.fractional seconds] and requires those additional 4 bytes of storage I mentioned for a total of 8 bytes.
New Data Types Supported in Microsoft SQL Server 2008
Microsoft embraced ISO compliancy in regards to storing date and time data with the release of SQL Server 2008, specifically two of the four new date and/or time data types finally segregate that information. Let's examine these data types closer.
Date Data Type (New in SQL Server 2008)
SELECT col_datetime, col_date FROM tempusfugit GO
The date data type presents itself in simple YYYY-MM-DD format and should better fit the needs of most development scenarios than the current process of translating a datetime column's value via the CONVERT() function. Doing so also reduces database storage requirements significantly when compared to a standard datetime column. Storage requirements are only 3 bytes for the date data type. At this point I want to touch upon the question that prompted this tip: does SQL 2008 afford you any better storage options for date-only data value storage and retrieval. The answer is definitely "yes" and definitely "Date data type". In previous versions of SQL Server, you had three options for storing your date-only information:
- Store the values in a datetime-typed column and take a 5 byte storage penalty
- Store the values in a smalldatetime-typed column, assuming a 1 byte storage hit
- Store the value as a varchar(n) with the data stored in a format that meets the perceived expected output format (YYYY-MM-DD, MM/DD/YYYY, etc.)
Isn't it interesting that the most commonly-used date/time data type, datetime, is not the best option for efficiently storing that specific data? Unless you need to store fractional seconds, datetime is never the option for storing date/time-related data. Using a varchar(n) column to store your data in the format you plan on presenting to your end-users may seem like a good idea, but it would result in storage requirements of 10 bytes when you factor in separation characters and a four character year as well as two characters each for date and month. Additionally, I pose the question: "What happens if your usage expectations change or vary from user to user?" No, what was typical is that you had to resort to using the CONVERT() function in T/SQL to convert a stored datetime or smalldatetime value into a format you wished to present to the end-user as is shown in the following examples.
| T-SQL Command Executed | Result |
|---|---|
| SELECT CONVERT(VARCHAR(10),col_datetime, 101) FROM dbo.TempusFugit | 10/26/2008 |
| SELECT CONVERT(VARCHAR(23),col_datetime, 126) FROM dbo.TempusFugit | 2008-10-26T21:54:23.443 |
| SELECT CONVERT(VARCHAR(8),col_datetime, 112) FROM dbo.TempusFugit | 20081026 |
It's not that the CONVERT() function is complex, but rather the parameters require memorization or constant look-up in order to accommodate your formatting needs. SQL Server 2008 makes this process so much simpler by use of the date data type.
Datetime2 Data Type (New in SQL Server 2008)
SELECT col_datetime, col_datetime2 FROM tempusfugit GO
By contrast to the new date data type, the datetime2 data type that is new in Microsoft SQL Server 2008 expands the granularity of the original datetime data type to 100 nanoseconds in the format of YYYY-MM-DD HH:MM:SS[.fractional seconds]. Storage requirements depend upon the precision of the column, which is declared upon creation. Precision can be any value between 0 and 7, with the default being 7.
| Precision | Storage Requirements |
|---|---|
| < 3 | 6 bytes |
| 3 or 4 | 7 bytes |
| > 5 | 8 bytes |
It is interesting to note that a datetime column and a datetime2(3) column are identical, except in regards to storage requirements. The datetime field consumes 8 bytes, while the datetime2(3) column requires only 7 bytes of storage:
DECLARE @datetime AS datetime = GETDATE() DECLARE @datetime2 AS datetime2(3) = GETDATE() SELECT @datetime AS Value, DATALENGTH(@datetime) AS Storage_Bytes SELECT @datetime2 AS Value, DATALENGTH(@datetime2) AS Storage_Bytes
I have yet to find documentation explaining this behavior, but if you are dealing with large amounts of datetime data that requires storage of fractional seconds accurate to the 1/1000 of a second it would be more efficient to use the datetime2(3) format in lieu of the standard datetime data type.
Datetimeoffset Data Type (New in SQL Server 2008)
SELECT col_datetime, col_datetimeoffset FROM tempusfugit GO
The new datetimeoffset data type is useful when dealing with time-zone information. SQL 2005 data types simply stored date and time; there was no simplified way to standardize times across time zones. Datetimeoffset combines the datetime2 data type with an additional offset value that is useful for global databases. In our example with the GETDATE() function we did not include an offset, therefore the result was as you see above. However, let us assume that we are dealing with a global shipping company's database and they have standardized on Greenwich Mean Time. To enter a value into their Shipping table for a shipment made in the Eastern time zone in the United States your query may look something like this:
INSERT INTO dbo.Arrivals (ShippingID, Arrival_Scan_dt) VALUES (1234, '2008-08-26 11:32:30 -5:00')
Cost of storage follows the same guidelines as those for datetime2, with an additional 3 bytes required for storage of the added offset value. The offset value must fall in the range of +/-00:00 - +/-23:59.
Time Data Type (New in SQL Server 2008)
SELECT col_datetime, col_time FROM tempusfugit
The time data type is the counterpart to the date data type in that it is the logical outcome of splitting the datetime data type from previous versions of SQL Server. The format is identical to the time aspect of the datetime data type, with the exception that its precision is adjustable, as is the resulting storage cost from the table below.
| Precision | Storage Requirements |
|---|---|
| < 3 | 3 bytes |
| 3 or 4 | 4 bytes |
| > 4 | 5 bytes |
As you can see from our example, we created this particular col_time column as time(7) (the default). The GETDATE() function returns values as a datetime data type, which has a precision of 3. The behavior of the time data type is as such that it will not truncate trailing zeros. This is standard behavior across all date and time data types in SQL Server. It is important that you understand your data and make the proper decisions when creating the fields in your databases so as not to waste valuable storage space over the course of long term data storage.
Next Steps
- Review databases you have migrated (or plan to migrate) to SQL Server 2008 to determine if any benefit exists for converting your datetime data to one of the new data types available in SQL Server 2008.
- Learn more about the CONVERT() function and its use in date and time presentation.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
