In 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

T-SQL
1
2
declare @d date = getdate()
select @d
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

T-SQL
1
2
declare @d date = '1600-01-01'
select @d
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

T-SQL
1
2
declare @d datetime = '2011-11-30'
select @d + 1
declare @d datetime = '2011-11-30'
select @d + 1

2011-12-01 00:00:00.000

T-SQL
1
2
declare @d date = '2011-11-30'
select @d + 1
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

T-SQL
1
2
declare @d date = '2011-11-30'
select DATEADD (dd,1,@d)
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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

T-SQL
1
2
3
4
declare @d datetime2 ='2011-11-30 19:23:52.5433840'
select convert(time,@d) 
union all
select @d
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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

T-SQL
1
2
declare @d datetime2 = '2011-11-30'
select @d + 1
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

T-SQL
1
2
3
4
5
6
declare @d2 datetime2 = getdate()
select @d2,getdate()
GO
 
declare @d2 datetime2 = SYSDATETIME()
select @d2,SYSDATETIME()
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

T-SQL
1
select SYSUTCDATETIME(), 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