Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server DateTime2(0) vs Date

Tags:

sql

sql-server

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?

like image 282
Oliver Kane Avatar asked Apr 06 '15 19:04

Oliver Kane


People also ask

Is DateTime2 better than DateTime?

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.

What is DateTime2 vs DateTime in SQL Server?

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.

What does DateTime2 0 mean?

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.

What is the DateTime2 format in SQL?

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.


1 Answers

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.

like image 164
M.Ali Avatar answered Sep 17 '22 23:09

M.Ali