XML FeedsIn my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.
In day 1 of our 25 days of SQL improvements/upgrades we will talk about dates and time. I remember my first day at a job, I looked at some of the biggest tables and all the dates were stored as integers. My first though was WTF? Then I learned that they couldn't use smalldates because they needed dates before 1900-01-01 and in order to save 4 bytes per row it was decided to use integers instead of the datetime datatype. Using integers instead of dates opens a nice big can of worms, first you can't use date arithmetic, and if you do arithmetic you might get invalid dates, second anyone can insert invalid dates.
Lucky for you SQL Server 2008 introduced 4 new date related data types, these are date, datetime2, time and datetimeoffset. In this post I will only look at date, datetime2 and time.
Date
The date data type only stores the date portion of a regular datetime. If you run the following on November 30th 2011
- declare @d date = getdate()
- select @d
You get back the following
2011-11-30
The nice thing about the date datatype is that the range spans from January 1, 1 A.D. through December 31, 9999 A.D.
No problem running this
- declare @d date = '1600-01-01'
- select @d
If you tried that with a datetime, you will get the following friendly message
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
It takes only 3 bytes to store a date, so if you only need to store the date portion you will save 5 bytes compared to a datetime, you will even save space if you switch from integers
If you are used to add or subtract dates by using +1 or -1, be warned
- declare @d datetime = '2011-11-30'
- select @d + 1
2011-12-01 00:00:00.000
- declare @d date = '2011-11-30'
- select @d + 1
Msg 206, Level 16, State 2, Line 2
Operand type clash: date is incompatible with int
You should be using DATEADD instead, like this
- declare @d date = '2011-11-30'
- select DATEADD (dd,1,@d)
See also this post which has some more detail Operand type clash: date is incompatible with int error when trying to do +1 on a date data type in SQL Server 2008
Time
The time data type will only hold the time portion of a datetime2, the range goes from 00:00:00.0000000 through 23:59:59.9999999. This was a very needed data type, now you can store the date and the time separate and if you index both columns, you will get much better performance than if you used just a datetime column
You can specify precision for the time data type, in the code below you can see the output for all precisions between 0 and 7
- declare @d time ='2011-11-30 19:23:52.1234567'
- select '(0)', convert(time(0),@d) union all
- select '(1)', convert(time(1),@d) union all
- select '(2)', convert(time(2),@d) union all
- select '(3)', convert(time(3),@d) union all
- select '(4)', convert(time(4),@d) union all
- select '(5)', convert(time(5),@d) union all
- select '(6)', convert(time(6),@d) union all
- select '(7)', convert(time(7),@d) union all
- select '@d', convert(time,@d) union all
- select '--', @d
Output
(0) 19:23:52.0000000 (1) 19:23:52.1000000 (2) 19:23:52.1200000 (3) 19:23:52.1230000 (4) 19:23:52.1235000 (5) 19:23:52.1234600 (6) 19:23:52.1234570 (7) 19:23:52.1234567 @d 19:23:52.1234567 -- 19:23:52.1234567
If you combine time and datetime by way of a UNION then you will get datetime2 for both
- declare @d datetime2 ='2011-11-30 19:23:52.5433840'
- select convert(time,@d)
- union all
- select @d
Output
1900-01-01 19:23:52.5433840
2011-11-30 19:23:52.5433840
Datetime2
The datetime2 data type has a range of January 1,1 AD through December 31, 9999 AD for the date and 00:00:00 through 23:59:59.9999999 for the time.
Just like with the time data type, you can specify the precision, if you don't specify anything then the 7 is assumed. Let's take a look, run the following code
- declare @d datetime2 ='2011-11-30 19:23:52.1234567'
- select '(0)', convert(datetime2(0),@d) union all
- select '(1)', convert(datetime2(1),@d) union all
- select '(2)', convert(datetime2(2),@d) union all
- select '(3)', convert(datetime2(3),@d) union all
- select '(4)', convert(datetime2(4),@d) union all
- select '(5)', convert(datetime2(5),@d) union all
- select '(6)', convert(datetime2(6),@d) union all
- select '(7)', convert(datetime2(7),@d) union all
- select '@d', convert(datetime2,@d) union all
- select '--', @d
Output
(0) 2011-11-30 19:23:52.0000000 (1) 2011-11-30 19:23:52.1000000 (2) 2011-11-30 19:23:52.1200000 (3) 2011-11-30 19:23:52.1230000 (4) 2011-11-30 19:23:52.1235000 (5) 2011-11-30 19:23:52.1234600 (6) 2011-11-30 19:23:52.1234570 (7) 2011-11-30 19:23:52.1234567 @d 2011-11-30 19:23:52.1234567 -- 2011-11-30 19:23:52.1234567
Just as with dates, you can't do something like this
- declare @d datetime2 = '2011-11-30'
- select @d + 1
You need to use the DATEADD function instead
If you want to use the current date and time and store that in a datetime variable then make sure not to use GETDATE, use SYSDATETIME instead
- declare @d2 datetime2 = getdate()
- select @d2,getdate()
- GO
- declare @d2 datetime2 = SYSDATETIME()
- select @d2,SYSDATETIME()
Output
2011-11-30 21:14:07.0430000 2011-11-30 21:14:07.043 2011-11-30 21:14:07.1277166 2011-11-30 21:14:07.1277166
As you can see, when using GETDATE, the full precision is not captured.
If you want to use UTC date and time then use SYSUTCDATETIME instead of GETUTCDATE
- select SYSUTCDATETIME(), GETUTCDATE()
Output
2011-12-01 02:17:05.7909356 2011-12-01 02:17:05.790
If you have any code like this
- select DATEADD(ms,-3,'20111201')
2011-11-30 23:59:59.997
That code is used to specify the end boundary of a range so that between can be used. You will be missing some rows if you use the higher precision
Always use >= @date1 AND < @date2, where @date2 in this case would be 2011-12-01, this is safe for all the date data types
That is it for day 1, hopefully you have picked up a tip or two, come back tomorrow for post number two
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Awesome post, look forward to the following ones! 
While the new date type largely resolves the date limits in datetime, it is often useable for much of my work. I often need to store BC dates, which is still outside of the range.
The only thing I thank think of is having a bit column indicating if it is positive or negative. You still have to be careful especially when going from BC to AD (there is no year 0) 
Really good explaination...
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.