In this post, I’ll cover the new date and time support provided by SQL Server 2008. This is an area in which any single enhancement may not rock your world, but collectively, I think you’ll agree that Microsoft has done a very good job overhauling the date and time support in SQL Server.
Separate Date and Time Data Types
We database developers have long been clamoring for the ability to store dates and times as separate types, and SQL Server 2008 now finally delivers. (Small thing, but what took them so long?) If you just need a date, use the new date data type. If you just need a time, use the new time data type. It’s as simple as that:
DECLARE @DOB date DECLARE @MedsAt time SET @DOB = '2008-03-24' SET @MedsAt = '13:00:00' SELECT @DOB AS DOB, @MedsAt AS MedsAt
And the output:
DOB MedsAt ---------- ---------------- 2008-03-24 13:00:00.0000000
New and Obsolete Data Types
In total, there are four new data types for dates and times in SQL Server 2008:
- date
- time
- datetime2
- datetimeoffset
The new datetime2 data type is intended to replace the traditional datetime data type. For all new database development, you should no longer use datetime; you should use datetime2 instead. In addition, the smalldatetime data type is also considered obsolete, and should no longer be used for new development.
More Portable Dates
The new date, datetime2, and datetimeoffset all support a greater range of values than the old datetime and smalldatetime data types. Specifically, they are now aligned with .NET, Windows, and the SQL standard, being capable of storing any date in the range of 1/1/0001 through 12/31/9999. This is the same range of dates available in .NET, from DateTime.MinValue to DateTime.MaxValue.
The old datetime data type, in contrast, cannot store dates earlier than the year 1753. This means that date values can now move back and forth between the database and your .NET application without needing to worry about the potential for errors occurring with dates earlier than 1753. Even better, despite the greater range, dates in the new types are compacted to fit in only 3 bytes, where 4 bytes are needed to store the smaller range of dates in the old datetime data type.
More Portable Times
The new time, datetime2, and datetimeoffset all support greater fractional second precision than the old datetime and smalldatetime data types. Specifically, they are now aligned with the .NET, Windows, and the SQL standard, and can store times with up to 7 digits of fractional second precision (accurate to within 100ns, or one 10-millionth of a second). This is the same degree of fractional second precision offered by time values in .NET.
The old datetime data type, in contrast, supports only 3 digits of fractional second precision. This means that your time values are preserved with full fidelity (no data loss) as they pass back and forth between the database and your .NET application. (The old smalldatetime data type doesn’t even store seconds, and is accurate only to the minute.)
Time Zone Awareness
If you’ve ever built applications that need to cope with multiple time zone, you’re just going to love this new feature. The datetimeoffset data type provides the same range of dates (1/1/0001 through 12/31/9999) and fractional second precision (100ns) as the new datetime2 data type, and also includes a time zone portion. Specifically, datetimeoffset stores an offset ranging from -14:00 through +14:00 in addition to the date and time value. This allows you to store local dates and times in different regions of the world, and let SQL Server handle the differences automatically. Values appear to go in and come out as local dates and times, but internally, they are all stored in UTC (Universal Coordinated Time). This means that comparisons, sorting, and indexing all work as you’d expect (for example, 9am in NY is treated as later than 7am in California). All you do is append the time zone as part of the local date and time value to be stored in the datetimeoffset data type, and SQL Server normalizes and denormalizes the value between UTC and the specified local time zone for you automatically as you store and retrieve the data!
Two new functions are provided to work with time zone awareness. They are TODATETIMEOFFSET and SWITCHOFFSET. The TODATETIMEOFFSET function converts a datetime or datetime2 (time zone-less) value to a datetimeoffset (time zone-aware) value, based on the offset (time zone between -14:00 and +14:00) passed to the function. The SWITCHOFFSET function converts the local date/time value stored in a datetimeoffset value to any other time zone, passed in as an offset to the function.
Here’s an example that demonstrates how SQL Server recognizes and accounts for time zone differences in datetimeoffset data type instances:
-- Time zone awareness DECLARE @Time1 datetimeoffset DECLARE @Time2 datetimeoffset DECLARE @MinutesDiff int SET @Time1 = '2007-11-10 09:15:00-05:00' -- NY time is GMT -05:00 SET @Time2 = '2007-11-10 10:30:00-08:00' -- LA time is GMT -08:00 SET @MinutesDiff = DATEDIFF(minute, @Time1, @Time2) SELECT @MinutesDiff
And the output:
MinutesDiff ----------- 255
A 255-minute difference between the two times amounts to 4 hours and 15 minute difference, so SQL Server clearly accounted for the three hour time difference between the NY and LA time zones.
One pain point remains, unfortunately. The datetimeoffset data type is not DST (Daylight Savings Time) aware. You’ll still need to handle DST on your own at the application level. The murmur around Redmond at the time I was writing Programming Microsoft SQL Server 2008 was that DST support is planned for a future version of SQL Server.
Date/Time Accuracy and Storage
As I already mentioned, date values are stored in 3 bytes, which is 1 byte less than the 4-byte date portion of the old datetime data type. So we’re offered a greater range of values that get stored in less space than before. Hey, that doesn’t happen every day!
What about times? Well, they’re variable. Meaning that even though SQL Server now supports full 7-digit fractional second precision, it’s not compulsory. You can choose any number of digits of fractional second precision that you require, from 0 (none) to 7 (100ns), referred to as the scale. (If not specified, the default scale is 7.) So if you’re OK with losing fractional second precision of .NET times that get stored in your database, you can choose a scale of 0, and only 3 bytes will be required to store the time. You specify the scale in parentheses after the data type name. For example:
DECLARE @NoPrecision time(0) DECLARE @SomePrecision time(4) DECLARE @MaxPrecision time(7) SET @NoPrecision = '13:25' SET @SomePrecision = '13:25' SET @MaxPrecision = '13:25' SELECT @NoPrecision AS NoPrecision, @SomePrecision AS SomePrecision, @MaxPrecision AS MaxPrecision
Although we didn’t hard-code any fractional seconds (or seconds, for that matter), the output indicates that SQL Server honors the specified precision scale:
NoPrecision SomePrecision MaxPrecision ---------------- ---------------- ---------------- 13:25:00 13:25:00.0000 13:25:00.0000000
Extracting Dates and Times
It’s also nice to know that you can use CAST and CONVERT to extract just the date or time portion of an indexed datetime2 column, without losing the benefit of the index. For example:
CREATE TABLE dbo.Search(MyDate datetime2) CREATE CLUSTERED INDEX idx1 ON dbo.Search(MyDate) -- Insert some rows into dbo.Search... SELECT MyDate FROM dbo.Search WHERE CONVERT(date, MyDate) = '2005-04-07'
That means no more ugly queries where you need to check for the inclusive range of times in any date of interest (that is, from 0:00:00.0000000 to 23:59:59.9999999). In preceding query, SQL Server is able to leverage the index created on the MyDate column, and doesn’t need to resort to a sequential table scan instead.
New and Changed Functions
It should come as no surprise that all the traditional functions, including DATEADD, DATEDIFF, DATEPART, and DATENAME all work just the same with the new data types as the old ones. Furthermore, DATEPART and DATENAME now support additional new date parts targeting new SQL Server 2008 date/time components; specifically, mcs (microseconds), ns (nanoseconds), and tz (time zone).
There are also three new functions that return the current date and time on the server clock. Two of them, SYSDATETIME and SYSUTCDATETIME are equivalent to GETDATE and GETUTCDATE respectively, except that they return a datetime2 value instead of a datetime value. The new SYSDATETIMEOFFSET function also returns the current server date/time, but as a datetimeoffset data type which actually is DST-aware, based on the DST setting in the Control Panel Date and Time applet (for example, it returns -4:00 for NY during DST, even though NY is actually at -5:00 GMT).
Summary
Dates and times have gotten a huge boost in SQL Server 2008. In this post, I explained the four new date/time data types, and the new functions provided to support them. These new types are first-class citizens, and are supported across ODBC, OLE-DB, ADO.NET, SSIS (Integration Services), SSAS (Analysis Services), SSRS (Reporting Services), and replication. So stop using datetime and smalldatetime, and start enjoying the power of the new SQL Server 2008 date and time data types!
Lenni's Technology Blog 