Time for a Date?
As a DBA I wear many hats! If I had to choose a hat for my data architect role it would probably be a nice white panama, the sort you see in the Agatha Christie re-runs on ITV3, but I digress!.
As a data architect you 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), so 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:
SELECT CAST('20080101' AS datetime) as DateOnly
,CAST('14:00:00.000' AS datetime) as TimeOnly
Returns:
| DateOnly | TimeOnly |
| 2008-01-01 00:00:00.000 | 1900-01-01 14:00:00.000 |
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.
| Specified scale | Result (precision, scale) | Column length (bytes) | Fractional seconds precision |
| time | (16,7) | 5 | 7 |
| time(0) | (8,0) | 3 | 0-2 |
| time(1) | (10,1) | 3 | 0-2 |
| time(2) | (11,2) | 3 | 0-2 |
| time(3) | (12,3) | 4 | 3-4 |
| time(4) | (13,4) | 4 | 3-4 |
| time(5) | (14,5) | 5 | 5-7 |
| time(6) | (15,6) | 5 | 5-7 |
| time(7) | (16,7) | 5 | 5-7 |
So the queries:
SELECT CAST('14:13:12.1234567' AS time) AS [time]
,CAST('14:13:12.1234567' AS time(0) ) AS [time(0)]
,CAST('14:13:12.1234567' AS time(1) ) AS [time(1)]
,CAST('14:13:12.1234567' AS time(2) ) AS [time(2)]
,CAST('14:13:12.1234567' AS time(3) ) AS [time(3)]
, CAST('14:13:12.1234567' AS time(4) ) AS [time(4)]
,CAST('14:13:12.1234567' AS time(5) ) AS [time(5)]
,CAST('14:13:12.1234567' AS time(6) ) AS [time(6)]
,CAST('14:13:12.1234567' AS time(7) ) AS [time(7)]
Returns:
| time | time(0) | time(1) | time(2) | time(3) |
| 14:13:12.1234567 | 14:13:12 | 14:13:12.1000000 | 14:13:12.1200000 | 14:13:12.1230000 |
| time(4) | time(5) | time(6) | time(7) | |
| 14:13:12.1235000 | 14:13:12.1234600 | 14:13:12.1234570 | 14:13:12.1234567 |
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
| Specified scale | Result (precision, scale) | Column length (bytes) | Fractional seconds precision |
| Datetime2 | (27,7) | 8 | 7 |
| Datetime2(0) | (19,0) | 6 | 0-2 |
| Datetime2(1) | (21,1) | 6 | 0-2 |
| Datetime(2) | (22,2) | 6 | 0-2 |
| Datetime(3) | (23,3) | 7 | 3-4 |
| Datetime(4) | (24,4) | 7 | 3-4 |
| Datetime(5) | (25,5) | 8 | 5-7 |
| Datetime(6) | (26,6) | 8 | 5-7 |
| Datetime(7) | (27,7) | 8 | 5-7 |
So the queries:
SELECT CAST('2008-05-29 14:13:12.1234567' AS datetime2) AS [datetime2]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (0) ) AS [datetime2(0)]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (1) ) AS [datetime2(1)]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (2) ) AS [datetime2(2)]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (3) ) AS [datetime2(3)]
, CAST('2008-05-29 14:13:12.1234567' AS datetime2 (4) ) AS [datetime2(4)]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (5) ) AS [datetime2(5)]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (6) ) AS [datetime2(6)]
,CAST('2008-05-29 14:13:12.1234567' AS datetime2(7) ) AS [datetime2(7)]
Returns:
| datetime2 | datetime2(0) | datetime2(1) | datetime2(2) | datetime2(3) |
| 2008-05-29 14:13:12.1234567 | 2008-05-29 14:13:12.0000000 | 2008-05-29 14:13:12.1000000 | 2008-05-29 14:13:12.1200000 | 2008-05-29 14:13:12.1230000 |
| datetime2(4) | datetime2(5) | datetime2(6) | datetime2(7) | |
| 2008-05-29 14:13:12.1235000 | 2008-05-29 14:13:12.1234600 | 2008-05-29 14:13:12.1234570 | 2008-05-29 14:13:12.1234567 |
Note: This gives the wrong precision when using the February CTP.
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:
datetime2
2008-05-29 00:00:00.0000000
Note: This gives the wrong precision when using the February CTP.
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
| Specified scale | Result(precision, scale) | Column length (bytes) | Fractional seconds precision |
| datetimeoffset | (34,7) | 10 | 7 |
| datetimeoffset (0) | (26,0) | 8 | 0-2 |
| datetimeoffset(1) | (28,1) | 8 | 0-2 |
| datetimeoffset(2) | (29,2) | 8 | 0-2 |
| datetimeoffset(3) | (30,3) | 9 | 3-4 |
| datetimeoffset(4) | (31,4) | 10 | 3-4 |
| datetimeoffset(5) | (32,5) | 10 | 5-7 |
| datetimeoffset(6) | (33,6) | 10 | 5-7 |
| datetimeoffset(7) | (34,7) | 10 | 5-7 |
The following are all the same moment:
SELECT CAST('2008-05-29 16:28:12.1234567 +02:15' AS datetimeoffset(7)) AS 'datetimeoffset'
,CAST('2008-05-29T11:58:12.1234567-02:15' AS datetimeoffset(7)) AS 'datetimeoffset IS08601'
,CAST('2008-05-29 14:13:12.1234567Z' AS datetimeoffset(7)) AS 'datetimeoffset UTC'
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.
| Datepart | Abbreviation |
| microsecond | mcs |
| nanosecond | ns |
| TZoffset | tz |
| ISO_WEEK | isowk, isoww |
For example:
DECLARE @datetime2 datetime2(7), @datetimeoffset datetimeoffset
SET @datetime2 = '2008-05-29T14:13:12.1234567'
SET @datetimeoffset = '2008-05-29T14:13:12.1234567+02:15'
/* Using full names */
SELECT DATEPART ( microsecond, @datetime2 ) AS [Microsecond]
,DATEPART ( nanosecond, @datetime2 ) AS [Nanosecond]
,DATEPART ( TZoffset, @datetimeoffset ) AS [TZoffset]
,DATEPART ( ISO_WEEK, @datetime2 ) AS [ISO_Week]
/* Using abbreviations */
SELECT DATEPART ( mcs, @datetime2 ) AS [Microsecond]
,DATEPART ( ns, @datetime2 ) AS [Nanosecond]
,DATEPART ( tz, @datetimeoffset ) AS [TZoffset]
,DATEPART ( isowk, @datetime2 ) AS [ISO_Week]
Both queries return the result set
| Microsecond | Nanosecond | TZoffset | ISO_Week |
| 123456 | 123456700 | 135 | 22 |
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.
DECLARE @datetime2_1 datetime2(7), @datetime2_2 datetime2
SELECT @datetime2_1 = '2008-05-29T14:13:12.1234567',
@datetime2_2 = '2008-05-29T14:13:12.1234578'
SELECT DATEDIFF( mcs, @datetime2_1, @datetime2_2 ) AS [Microseconds Difference]
,DATEDIFF( ns, @datetime2_1, @datetime2_2 ) AS [Nanoseconds Difference]
Returns:
| Microseconds Difference | Nanoseconds Difference |
| 1 | 1100 |
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
DECLARE @datetime2 datetime2(7), @datetimeoffset datetimeoffset, @date date
SELECT @datetime2 = '2008-05-29T14:13:12.1234567'
,@datetimeoffset = '2008-05-29T14:13:12.1234567'
,@date = '2008-05-29'
SELECT DAY(@datetime2)
,DAY(@datetimeoffset)
,DAY(@date)
,MONTH(@datetime2)
,MONTH(@datetimeoffset)
,MONTH(@date)
,YEAR(@datetime2)
,YEAR(@datetimeoffset)
,YEAR(@date)
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.
SELECT SYSDATETIME() AS [SysDateTime]
,SYSDATETIMEOFFSET() AS [SysDateTimeOffset]
,SYSUTCDATETIME() AS [SysUTCDateTime]
Returns:
| SysDateTime | SysDateTimeOffset | SysUTCDateTime |
| 2008-05-29 15:28:51.1202384 | 2008-05-29 15:28:51.1202384 +01:00 | 2008-05-29 14:28:51.1202384 |
This reflects that we are currently on British Summer Time. If you change the timezone to pacific time (GMT – 8:00) the results would be something like:
| SysDateTime | SysDateTimeOffset | SysUTCDateTime |
| 2008-05-29 07:29:47.6967536 | 2008-05-29 07:29:47.6967536 -07:00 | 2008-05-29 14:29:47.6967536 |
Note: This gives the wrong precision/format when using the February CTP.
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 )
SELECT SYSDATETIMEOFFSET() as [Current Time (BST)]
,SWITCHOFFSET(SYSDATETIMEOFFSET(), '-07:00' ) AS [Pacific Summer Time]
Returns:
| Current Time (BST) | Pacific Summer Time |
| 2008-05-29 21:32:10.4515504 +01:00 | 2008-05-29 13:32:10.4515504 -07:00 |
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 )
CREATE TABLE #tmpdts (datetimecol1 datetime2, datetimecol2 datetime2, datetimecol3 datetime2 )
INSERT INTO #tmpdts ( datetimecol1, datetimecol2, datetimecol3 )
VALUES( SYSDATETIME(), SYSDATETIME(), SYSDATETIME())
UPDATE #tmpdts
SET datetimecol2 = TODATETIMEOFFSET(datetimecol2, '-07:00' )
,datetimecol3 = TODATETIMEOFFSET(datetimecol3, -420 )
SELECT datetimecol1, datetimecol2, datetimecol3 FROM #tmpdts
DROP TABLE #tmpdts
Returns:
| datetimecol1 | datetimecol2 | datetimecol3 |
| 2008-05-29 21:47:36.6533632 +01:00 | 2008-05-29 13:47:36.6533632 -07:00 | 2008-05-29 13:47:36.6533632 -07:00 |
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 the next release of SQL Server. These will help improve storage and 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