Datetime Vs Datetime2 datatypes

One of my friends asked me if there is any difference between Datetime and Datetime2 datatypes other than the difference in the millisecond fractional values (3 digits for Datetime and 7 digits for Datetime2).

Yes there are other differences too.

Datetime:
Minimum value is 1753-01-01
It requires 8 bytes to store values
The last digit of the millisecond is always 0,3 or 7
Millisecond can be expressed using either colon or a dot

declare @datetime datetime
select @datetime='2015-10-10 14:22:34.168'
select @datetime

GO

declare @datetime datetime
select @datetime=‘2015-10-10 14:22:34:168’
select @datetime

GO

The result is 2015-10-10 14:22:34.167

Datetime2
Minimum value is 0001-01-01
It requires 6 to 8 bytes depends on number of milliseconds to store values
Millisecond can be expressed using only dot but using a colon will throw an error

declare @datetime datetime2(7)
select @datetime='2015-10-10 14:22:34.1682345'
select @datetime

The result is 2015-10-10 14:22:34.1682345

If you use a colon

declare @datetime datetime2(7)
select @datetime='2015-10-10 14:22:34:1682345'
select @datetime

you get an error

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

You may need to read this post on Millisecond values and separator in time values

Advertisements

2 thoughts on “Datetime Vs Datetime2 datatypes

  1. One other important point is you can use integer arithmetic with datetime whereas you cant use integer arithmetic with datetime2. You need to explicitly use date functions for performing date manipulation
    Example
    DECLARE @dt datetime,@dt2 datetime2
    SELECT @dt = GETDATE(),@dt2 = GETDATE()

    –This works
    SELECT @dt+1

    –This throws an error as below and results in batch abortion
    –Msg 206, Level 16, State 2, Line 8
    –Operand type clash: datetime2 is incompatible with int
    SELECT @dt2 + 1

    –Both works
    SELECT DATEADD(dd,1,@dt)
    SELECT DATEADD(dd,1,@dt2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s