Date and time manipulation is integral to most applications. Until SQL Server 2008, SQL Server didn’t have separate data types for date and time. SQL Server 2008 not only provides separate DATE and TIME data types but also introduces other date- and time-related data types, new functions, and enhancements to existing functions to support the new data types. In this article I explore the new date- and time-related data types and functions, and I cover a few issues that are important to consider regarding compatibility with older data types.
Date and Time Data Types
Prior to SQL Server 2008 we had two date- and time-related data types—DATETIME and SMALLDATETIME. These types have several limitations. Probably the biggest limitation is that they contain both date and time components that are inseparable. If you need to store only dates, the common approach is to always store the values at midnight; if you need only time, you store all values at the base date, January 1, 1900. Anyway you look at it, it’s awkward. Also, when you need to store only dates or only times, you still pay the full storage requirements (8 bytes for DATETIME, and 4 bytes for SMALLDATETIME). The precision of DATETIME is 3.333 milliseconds. For some systems, that’s not enough. Also, the range of dates supported by DATETIME starts with January 1, 1753. For systems that need to store older dates, DATETIME isn’t useful. Finally, the older data types have no time zone support.
SQL Server 2008 introduces four new data types that address most of these limitations. Table 1 lists all date- and time-related data types that are supported in SQL Server 2008, including the old ones. For each data type, Table 1 provides the storage in bytes, supported date range, precision, and recommended entry format.
As you can see in Table 1, the DATE data type requires only 3 bytes of storage, and TIME requires 3 to 5 bytes. That’s a big savings compared with DATETIME. With the TIME data type (and the other new types that contain a time component: DATETIME2 and DATETIMEOFFSET), you can define the precision in terms of a fraction of a second (hence the storage is expressed as a range). You do so by specifying a number in the range 0 through 7 in parentheses after the type name, as in TIME(3). The value 0 means a whole second accuracy, 3 means millisecond accuracy, and 7 (the default) means 100 nanoseconds accuracy. Note that 7 is the default, so bear in mind that if you don’t specify a value, you will pay the maximum storage requirement for the type. So as a best practice, I recommend always specifying the value based on your actual needs, rather than leaving it with the default 7.
The new data types that contain a date component (DATE, DATETIME2, and DATETIMEOFFSET) support dates starting with the year 0001. So systems that need to keep track of dates prior to 1753 can now do so.
As you probably figured, the DATETIME2 data type is simply a merge of the new DATE and TIME data types. You can think of it as an improved version of the older DATETIME data type. (During the prerelease period of SQL Server 2008, I remarked to some SQL Server MVPs and Microsoft folks that I found the name DATETIME2 amusing, and I jokingly suggested using DATETIMETOO instead. One of the other SQL Server MVPs then suggested enhancing the name to DATETIMETOOLATE, and yet another suggested DATETIMETOOMUCH. Unfortunately, none of our witty suggestions ultimately made the final release.)
The DATETIMEOFFSET data type gives you all the functionality you get from DATETIME2, plus a time zone component. Unfortunately, though, it doesn’t have a daylight savings component. Courtesy of SQL Server MVP Steve Kass, an important note about working with the DATETIMEOFFSET data type is the way the DATEDIFF function behaves when operating on values of this type. First, without looking at the documentation, try to predict the result of the following expression:
SELECT
DATEDIFF(day,
'2009-02-12 12:00:00.0000000 -05:00',
'2009-02-12 22:00:00.0000000 -05:00') AS days;
Intuition tells you that if both values are of the same time zone, the calculation should be done locally, producing 0 in this case. And if the time zones are different, you’d expect to get an error. However, that’s not what happens in practice. SQL Server first switches the offsets of both values to Coordinated Universal Time (UTC), then performs the calculation. When the offsets of the input values in the expression are switched to UTC, the first value remains in the date February 12, 2009 (19:00 as the hour), whereas the second value crosses a day boundary to February 13, 2009 (3:00 as the hour). So the expression returns 1. If you need the calculation to be handled locally, provided that the offsets of both values are the same, you need to first convert the values to a data type that doesn’t have a time zone component (e.g., DATETIME2), like so:
DECLARE
@dto1 AS DATETIMEOFFSET = '2009-02-12 12:00:00.0000000 -05:00',
@dto2 AS DATETIMEOFFSET = '2009-02-12 22:00:00.0000000 -05:00';
SELECT
CASE
WHEN DATEPART(TZOFFSET, @dto1) = DATEPART(TZOFFSET, @dto2)
THEN DATEDIFF(day, CAST(@dto1 AS DATETIME2), CAST(@dto2 AS DATETIME2))
ELSE NULL
END;
Note that the CASE expression performs the calculation with the converted local values only if their time zones are the same; otherwise it returns a NULL.
The last column in Table 1 provides a recommended entry format for literals of the corresponding data type and an example. Remember that some literal forms of date and time data are language-dependent. That is, their interpretation depends on the effective language in the session (which is based on the login’s default language setting), and more specifically, the effective DATEFORMAT option. Consider as an example the literal '02/12/09' that you specify when you enter data—for example, in a query such as the following:
SELECT <column_list> FROM SomeTable WHERE dt = '02/12/09';
Under us_english, which implicitly sets DATEFORMAT to mdy, this value is interpreted as February 12, 2009, when converted to a date and time data type. Under British (which sets DATEFORMAT to dmy), the value is interpreted as December 2, 2009. Under Japanese (which sets DATEFORMAT to ymd), the value is interpreted as December 9, 2002. Some formats are language-neutral, meaning that they’re always interpreted the same regardless of the effective language/DATEFORMAT setting. The last column in Table 1 provides one recommended language-neutral format for each date and time data type. For more language-neutral formats, see Table 2.
You might have noticed that Table 2 specifies the ISO format 'YYYY-MM-DD' as a language-neutral format for the new data types that were introduced in SQL Server 2008 but not for the older data types. This isn’t an oversight. Unfortunately, this format is language-dependent with respect to the older types, but for backward compatibility, it remains language-dependent for the older types even in SQL Server 2008. For example, the literal '2009-02-12' is interpreted as February 12, 2009, under us_english but as December 2, 2009, under British. For this reason, I strongly recommend refraining from using this format until you’ve migrated all of your data and code to work with the newer types. When I need to specify only a date, my preference is the format 'YYYYMMDD' with no separators. This format is considered language neutral with all data types—old and new.
Date and Time Functions
Along with the new date- and time-related data types, SQL Server 2008 also introduces new functions, as well as enhances existing functions to support the new types. Three new functions return the current date and time: SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET. SYSDATETIME returns the current date and time as a DATETIME2 data type; SYSUTCDATETIME also returns the value as a DATETIME2 data type, but in UTC terms; SYSDATETIMEOFFSET returns the value as a DATETIMEOFFSET data type, which includes the time zone. To test these functions, run the following code:
SELECT SYSDATETIME() AS \\[SYSDATETIME\\], SYSUTCDATETIME() AS \\[SYSUTCDATETIME\\], SYSDATETIMEOFFSET() AS \\[SYSDATETIMEOFFSET\\];
SQL Server 2008 doesn’t provide separate functions for the current date only and the current time only, but you can easily get this information by casting one of the functions that returns the current date and time combined to DATE or TIME, like so:
SELECT CAST(SYSDATETIME() AS DATE) AS \\[current_date\\], CAST(SYSDATETIME() AS TIME) AS \\[current_time\\];
Interestingly, a predicate that casts a filtered column of a type that contains both date and time (DATETIME, SMALLDATETIME, DATETIME2, DATETIMEOFFSET) to DATE is still considered a search argument (SARG). SQL Server 2008 was coded to understand that index ordering is preserved after the casting, and therefore will consider using an index created on the filtered column. As an example, consider the following query:
SELECT <column_list> FROM SomeTable WHERE CAST(dt AS DATE) = '20090212';
If an index exists on the column dt, SQL Server’s optimizer will consider using an Index Seek against it.
When you upgrade to SQL Server 2008, you obviously won’t alter the types of all existing DATETIME and SMALLDATETIME columns to the new types overnight. If you need to operate on an input value (e.g., CURRENT_TIMESTAMP) and its time value to midnight in the meantime, you can cast the value to DATE, then cast the result to DATETIME (or SMALLDATETIME) like so:
SELECT CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS DATETIME) AS date_only;
Similarly, to set the date part to the base date, January 1, 1900, you can cast the input value to TIME, then back to DATETIME, like so:
SELECT CAST(CAST(CURRENT_TIMESTAMP AS TIME) AS DATETIME) AS date_only;
SQL Server 2008 introduces the SWITCHOFFSET function to switch the offset of a given DATETIMEOFFSET value to a requested one. SQL Server will do the math to change the local value in the target offset, so logically, in terms of the same time zone, it’s considered the same value. Here’s an example for switching the offset of a value expressed in Eastern time (-05:00) to Pacific time (-08:00):
DECLARE @dto AS DATETIMEOFFSET = '2009-02-12 12:30:15.1234567 -05:00'; SELECT SWITCHOFFSET(@dto, '-08:00');
This code returns the value '2009-02-12 09:30:15.1234567 -08:00'.
If you have a value of any of the date and time types, and you want to convert it to a DATETIMEOFFSET value in terms of some target time zone, use the TODATETIMEOFFSET function, as in:
DECLARE @dt2 AS DATETIME2 = '2009-02-12 12:30:15.1234567'; SELECT TODATETIMEOFFSET(@dt2, '-08:00');
This code returns the value '2009-02-12 12:30:15.1234567 -08:00'.
SQL Server 2008 enhances the existing date- and time-related functions such as DATEADD, DATEDIFF, DATEPART, DATENAME, and others to support the new data types. Where relevant, it also supports new parts: microsecond (mcs), nanosecond (ns), TZoffset (tz) for the time zone, and ISO_WEEK (isowk, isoww) to get the ISO week number. As an example, the following code returns the ISO week number of a given date:
DECLARE @d AS DATE = '20090212'; SELECT DATEPART(ISO_WEEK, @d) AS wk;
This code returns the value 7.
Additional Compatibility Issues with Older Date and Time Data Types
You need to consider several additional issues when migrating columns, variables, and parameters from the older data types to the new ones. The older data types supported adding and subtracting integers using the + and - operators, as in:
DECLARE @dt AS DATETIME; SET @dt = CURRENT_TIMESTAMP; SELECT @dt + 1;
The integer is interpreted as day units, so in this example, you add one day to the input value.
Similarly, you could convert values of the older types to integer and the other way around. The integer 0 represents the base date January 1, 1900, the integer -1 (read, minus 1) represents December 31, 1899, and so on. So the following example returns the value '1900-01-01 00:00:00.000':
SELECT CAST(0 AS DATETIME);
The new date- and time-related data types support neither the + and - operators with integers, nor the casting to and from an integer. To add or subtract days, you need to explicitly use the DATEADD function. This is something to keep in mind if you’re currently writing code in SQL Server 2005 or earlier. If you follow best practices today and avoid using this integer manipulation, you could save yourself trouble in the future. Your code will be easier to migrate to work with the new data types. Similarly, instead of representing dates with an integer, such as 0 for the base date and -1 for the day before the base date, a better approach is to represent dates with language-neutral character string literals (e.g., '19000101', '18991231') that will work with the new types.
Another issue that you might face when switching from the older to the newer data types has to do with range filters. Suppose that you stored both date and time data in a DATETIME column. You need to filter a date range (e.g., February 2009), and you use the following form of a filter:
SELECT <column_list> FROM SomeTable WHERE dt BETWEEN '20090201' AND '20090228 23:59:59.997';
Some database administrators use this form with 997 in the millisecond unit in the second value to avoid implicit rounding that you would get if you specify 999, since the precision of DATETIME is 3.333 milliseconds, and 999 isn’t a multiplication of 3.333. However, the new data types support an accuracy of 100 nanoseconds, so if you neglect to change the forms of your range filters, you might end up missing some rows. To avoid such problems, make sure to specify range filters with >= first point in the range, and < first point right after the range, as in:
This form will work correctly with all date and time data types—old and new.
Finally, if you performed manipulation of your older date and time data, relying on the internal binary representation of the data, keep in mind that the binary representation of the new types is completely different. You’d need to rewrite such code.
Much Improved
SQL Server 2008 finally introduces separate types for date and time data, plus other new types with improved date range support, improved precision, and time zone support. SQL Server also introduces new functions, as well as enhances the existing functions to support the new data types. Now we can hope for other improvements related to date and time data in a future version of SQL Server, such as support for a daylight savings part in the DATETIMEOFFSET type, and for a native Interval type!
