SQL: Date and time

Luckily 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

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

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

2011-12-01 00:00:00.000

T-SQL
1 2	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)

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

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

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

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

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()

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()

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

SOURCE