Difference between DateTime and DateTime2 DataType

DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date and Time value. As per MSDN, Microsoft Suggests to use this new Data Type for new work instead of DateTime.

Following table summarizes some of the major difference between this new DateTime2 and the old DateTime Data Type.

DateTime DateTime2[(n)]
Min Value 1753-01-01 00:00:00 0001-01-01 00:00:00
Max Value 9999-12-31 23:59:59.997 9999-12-31 23:59:59.9999999
Storage Size 8 Bytes 6 to 8 bytes
Note: Parameter n is optional and if it is not specified then fractional
seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds
precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take
7 bytes For fractional seconds precision >4 it will take 8 bytes
Usage Declare @now datetime Declare @now datetime2(7)
Compliance Is not an ANSI/ISO compliant Is an ANSI/ISO compliant
Current Date and Time function GetDate() – It returns DB Current Date and Time of DateTime Data Type

Example: SELECT GETDATE()
Result: 2011-09-16 13:23:18.767

SYSDATETIME()– It returns DB Current Date and Time of DateTime2 Data Type

Example:SELECT SYSDATETIME()
Result: 2011-09-16 13:23:18.7676720

+/- days WORKS

Example:
DECLARE
@nowDateTime DATETIME = GETDATE() SELECT @nowDateTime + 1
Result: 2011-09-17 13:44:31.247

FAILS – Need to use only DateAdd function

Example:
DECLARE
@nowDateTime2 DATETIME2=
SYSDATETIME()
SELECT
@nowDateTime2+1
Result: Msg 206, Level 16, State 2, Line 2
Operand type clash: datetime2 is incompatible with int

DateTime2 with fractional seconds precision of 3 is same as DateTime data type. And DateTime2(3) uses 7 bytes of storage instead of 8 byte which old DateTime datatype uses and it also provides higher date range (i.e. 0001-01-01 to 9999-12-31 ) compared to DateTime data type. Now let us see this with an example:

DECLARE @nowDateTime DATETIME = GETDATE(),
        @nowDateTime2 DATETIME2(3)= SYSDATETIME()

SELECT DATALENGTH(@nowDateTime) 'DateTime Storage Size',
       DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'
Result:
DateTime Storage Size  DateTime2 Storage Size
 --------------------- ----------------------
 8                     7

ALSO READ

24 thoughts on “Difference between DateTime and DateTime2 DataType

  1. very good article.. well designed and to the point. Slow students like me requires this type of tutorial. Where things are broken into pieces and effect is shown by example. thanks mate.

  2. A fascinating discussion is worth comment. I believe that you need
    to publish more on this issue, it may not be a taboo subject but generally people do not speak about such topics.
    To the next! All the best!!

  3. Nicely explained.

    can you please post blog/article/solution for removing duplicate records from table without adding Identity column & table having records in billions.

    Thanks
    Jagannath

  4. datetime2(3) is NOT the same as datetime. They will have the same number of digits, but the precision of datetime is 3.33ms, while the precision of datetime2(3) is 1ms.


    DECLARE @nowDateTime2 DATETIME2=SYSDATETIME()
    select @nowDateTime2,
    cast(@nowDateTime2 as datetime2(3)),
    cast(@nowDateTime2 as datetime)

    It will return the following three values:
    2014-09-10 12:15:30.5136440
    2014-09-10 12:15:30.514 — datetime2(3) uses typical rounding to whole millisecond
    2014-09-10 12:15:30.513 — datetime will round to 0, 3 or 7 milliseconds.

Leave a Reply

Your email address will not be published. Required fields are marked *