CODEWITHSUNDEEP
sqlsql-server
Oliver Kane
Oliver Kane

Reputation: 898

SQL Server DateTime2(0) vs Date

What are the implications of using SQL Server's DateTime2 with a precision of 0 to represent a date rather than the built in Date field.

In either case, my concern is to prevent accidental time entries, but are there storage or performance considerations I should take note of?

Upvotes: 13

Views: 23793

Answers (3)

SuperDre
SuperDre

Reputation: 194

Just a reminder which I ran into myself when I converted a couple of DATETIME2(0) columns to DATE to make sure it aligned better with the value in the column (date only).

When using DATE you cannot use things like SELECT MyDate + 1 FROM.. or WHERE MyDate>0 while when using DATETIME2 you can, at least not in MS-SQL. Ofcourse IMHO it doesn't make any sense why DATETIME2 will allow you to do it and DATE not.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

DateTime2(0) will store datetime with no decimal values i.e YYYY-MM-DD hh:mm:ss

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

Storing data just as dates will only store dates i.e YYYY-MM-DD without any time values. 

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

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

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

Date is half the size of DATETIME(0) hence it will also perform better since sql server will process less data and will save disk space as well.

Upvotes: 25

benjamin moskovits
benjamin moskovits

Reputation: 5458

It won't work. According to MSDN the minimum size of Datetime2 is six bytes and will contain hh:mm:ss so it can, and will, contain a time component (default of midnight). As other responders have noted you must use a date type to guarantee that not time portion is saved and will occupy three bytes.

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

Upvotes: 9

Related Questions