T-SQL: datetime2 vs datetime

I recently read in a MCTS training manual that from SQL2008 onwards, the datetime datatype was "mainly available for backwards compatibility" and that datetime2 should be preferred. This is also backed up by Books Online which gives the rather cute message, "Use the time, date, datetime2 and datetimeoffset data types for new work".

While I can't see a (date)time in the near future where datetime will be removed from the product, I thought i'd highlight the main differences between datetime and datetime2.

Precision

datetime only allows for precision up to every 3rd milisecond which essentially means that you can get rounding issues for times that are in fact different. Of course, in the majority of applications this level of accuracy isn't relevant but its an interesting point to note. Datetime2 addresses this by allowing you to specify the accuracy of seconds to 7 decimal places (sorry if thats the correct terminology!).

So the example from BOL looks like:

SELECT CAST('2007-05-08 12:35:29.123' AS DATETIME) AS 'datetime' ,
    CAST('2007-05-08 12:35:29. 1234567' AS datetime2(7)) AS 'datetime2'


Storage

The syntax for datetime2 means you can specify the accuracy of your date and this also has storage implications. All datetime fields take up 8 bytes but by using datetime2, storing an identical date i.e specifying a precision level of 3, only takes up 6 bytes. As you get more accurate, the storage required increases.

From BOL:
6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.


So if you want to mimic datetime behaviour using datetime2, you need only to specify datetime2(3). One caveat to this is with regard to rounding as datetime is only accurate to every 3rd ms meaning the last digit will always end in 0, 4 or 7 so the following does not yield equal results:

SELECT CAST('2007-05-08 12:35:29.128' AS DATETIME) AS 'datetime' ,
    CAST('2007-05-08 12:35:29. 128567' AS datetime2(3)) AS 'datetime2'



One thing I did note was that GETDATE() and CURRENT_TIMESTAMP both return a datetime so it'll be interesting to see if MS look to change this in the future.