Time for another Date?

This was first published on the Technet Industry Insiders blog on May 27, 2008, but I thought I’d bring it up-to-date and that it would also be useful to have it on my new blog as well!!

As a DBA I wear many hats! Wearing my database architect hat, I extol the virtues of “correctly data typing” in your design, such as not using varchar when char would be more appropriate, declaring a smallint or tinyint where a column can only be an integer less than or equal to 32,767 or 255 (respectively), but when it comes to dates and times SQL Server has until till now, forced you to compromise.

If you use the 8 byte datetime data type for only storing a date, the time portion will default to midnight, and if you only store a time the date portion will default to 1901-01-01 e.g.

Running the query:

Returns:

The datetime data type can hold date values from January 1, 1753, through December 31, 9999 and time values from 00:00:00 through 23:59:59.997 rounded to increments of .000, .003, or .007 seconds, which means that if you want better accuracy you will have to resort to holding your dates as character data, or as an offset, or possibly in SQL 2005 as a .NET data type, which in themselves can cause issues and added complexity. The smalldatetime data type uses 4 bytes to store dates at a lower precision than datetime. This gives it a date range of January 1, 1900, through June 6, 2079 and a time accuracy of 1 minute.

So using either the datetime or smalldatetime data types for storing only a date or a time, are using excessive storage and may not fulfil your requirements. Fortunately in SQL Server 2008 several new data types have been added which will help to resolve these issues. These data types are time, date, datetime2 and datetimeoffset and I will look at each of these separately

Time

The time data type has a range between 00:00:00.0000000 to 23:59:59.9999999 and will use 3 to 5 bytes depending on the precision. This makes the time accurate to 100 nanoseconds.

So the queries:

Returns:

As you can see the default format for a time data type is hh:mm:ss[.nnnnnnn]

Date

The date data type has a range 0001-01-01 to 9999-12-31 with the fixed granularity of 1 day. This will take 3 bytes of storage. The default format for a date data type is YYYY-MM-DD.

Datetime2

A variable precision date and time data type with a range 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 using between 6 to 8 bytes for storage

So the queries:

Returns:

Like the datetime data type the datatime2 and datetimeoffet data type will default a time portion to midnight 00:00:00.000 if not specified i.e.

SELECT CAST('2008-05-29' AS datetime2) AS [datetime2]

Returns:

Datetimeoffset

A timezone aware, variable precision date and time data type with a range 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 using between 8 to 10 bytes for storage. The timezone offset is based on a 24hr clock and can have the range -14:00 through +14:00. The two ISO 8601 formats YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] and YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC) are valid string representations of the datetimeoffset data type

The following are all the same moment:

For more information on ISO 8601 date formats see http://en.wikipedia.org/wiki/ISO_8601

Datepart types

With the addition of these new more precise data types, it makes sense that the functions such as DATEPART have also been updated to reflect the higher accuracy.

For example:

Both queries return the result set

TZoffset requires a datetimeoffset value to return a non-null value according to Books Online although the February CTP 0 is returned instead. The Timezone offset value is in minutes so 2 hours 15 minutes is (2x60)+15=135

ISO_WEEK returns the week for the ISO 8601 week-date system. Each week is associated with the year in which the Thursday in the week occurs. i.e. week 1 of 2008 (2008W01) ran from Monday 31 December 2007 to Sunday, 6 January 2008.

Returns:

ISO_WEEK is not supported as a datepart in the datediff function.

DAY, MONTH and YEAR changes

The functions DAY, MONTH and YEAR can be used on datetime2, dateoffset and date datatypes but not time

High Precision System Date and Time functions

Additional non-deterministic high precision functions have added to return the more accurate data types

• SYSDATETIME returns the current database time stamp as datetime2(7)
• SYSDATETIMEOFFSET similar to SYSDATETIME but also includes the database timezone offset as a datetimeoffset(7)
• SYSUTCDATETIME returns the current database time stamp as datetime2(7) as a UTC time.

Returns:

This reflects that we are currently on British Summer Time. If you change your computer’s timezone to pacific time (GMT – 8:00) the results would be something like:

DATE and Time modification functions

SWITCHOFFSET will return a datetimeoffset value into the time zone offset specified. The syntax of the function is:

• SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

So back in British Summer time we get:

Returns:

TODATETIMEOFFSET converts a local date and time value and a time zone offset to a datetimeoffset UTC value. The syntax of the function is:

• TODATETIMEOFFSET ( datetime, time_zone )

Returns:

Both of these functions are deterministic

Looking Forward

As you can see there has been a great deal of improvements and added functionality available to the database architect and designer in SQL Server 2008. These will help improve storage and data accuracy when using the new precision data types for date and time.

For more information on these new data types and functions check out the SQL Server 2008 Books Online at http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx