Menu
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?
282
Microsoft recommends using DateTime2 instead of DateTime as it is more portable and provides more seconds precision. Also, DateTime2 has a larger date range and optional user-defined seconds precision with higher accuracy.
The main difference is the way of data storage: while in Datetime type, the date comes first and then time, in Datetime2, 3 bytes, in the end, represents date part! For the time part, things become more complicated, because it depends on defined precision.
datetime2(0) - you don't need fractional seconds. datetime2(1-7) - you need fractional seconds of the specified precision. datetimeoffset(0-7) - you need date and time with time zone awareness. time(0-7) - you need time only (no date) with fractional seconds of the specified precision.
datetime2 descriptionYYYY is a four-digit number, ranging from 0001 through 9999, that represents a year. MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year. DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month.
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.
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
