SQL Server DateTime2 (0) vs Date

Question

What are the implications of using SQL Server DateTime2 with an accuracy of 0 to represent a date, rather than a built-in Date field.

In any case, my task is to prevent accidental time recordings, but are there any storage or performance considerations that I should take into account?

Answer 1

This will not work. According to MSDN, the minimum size of Datetime2 is six bytes and will contain hh: mm: ss so that it can and will contain a time component (the default is midnight). As other respondents noted, you should use a date type to ensure that the non-time part is preserved and takes three bytes.

https://technet.microsoft.com/en-us/library/bb677335%28v=sql.105%29.aspx

Answer 2

DateTime2(0) will store the date and time without decimal values. I YYYY-MM-DD hh:mm:ss

 SELECT CONVERT(DateTime2(0) , GETDATE()) RESULT: 2015-04-06 20:47:17 

Saving data in the same way as dates will store dates ie YYYY-MM-DD without any time values.

 SELECT CONVERT(Date , GETDATE()) RESULT: 2015-04-06 

If you are only interested in dates, use the DATE data type.

DATETIME2 will use 6 bytes for patches less than 3 , and DATE will use 3 bytes .

The date is half the size of DATETIME (0), therefore, it will also work better, since the sql server will process less data and save disk space.