Datetime2 VS Datetime in SQL Server

DateTime2 vs DateTime in SQL Server

The MSDN documentation for datetime recommends using datetime2. Here is their recommendation:

Use the time, date, datetime2 and
datetimeoffset data types for new
work. These types align with the SQL
Standard. They are more portable.
time, datetime2 and datetimeoffset
provide more seconds precision.
datetimeoffset provides time zone
support for globally deployed
applications.

datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

DateTime vs DateTime2 Time Range Discrepancy

The weird limit and behavior (such as the last digit always being 0, 3 or 7) is because of how the time portion is internally stored in DATETIME:

The time portion is stored as the number of ticks since 00:00:00 (1 and 2). Each tick is exactly 1/300th of a second. Keeping this in mind, the DATETIME type is only capable of storing values such as:

00:00:00.000 -- 0 ticks
00:00:00.003 -- 1 tick, 1/300 = 0.003333 sec
00:00:00.007 -- 2 ticks, 2/300 = 0.006666 sec
00:00:00.010 -- 3 ticks, 3/300 = 0.01 sec
00:00:00.997 -- 299 ticks, 299/300 = 0.996666 sec
00:00:01.000 -- 300 ticks, 300/300 = 1 second

Since it is not possible to store half or quarter ticks, DATETIME cannot store or represent the values in between. For example, if you try to store a value such as CAST('2000-01-01 00:00:00.999' AS DATETIME) it will be rounded to 2000-01-01 00:00:01.000.

datetimeoffset vs datetime2 for UTC on SQL Server

The datetimeoffset data type will allow comparison between different offsets of the same time. e.g.:

SELECT 'equal'
WHERE
CAST('2021-02-12 15:48:11.0677934 -01:00' AS datetimeoffset) = CAST('2021-02-12 16:48:11.0677934 +00:00' AS datetimeoffset).

If you are storing only UTC values (where the offset is always zero), you can save storage space with datetime2. datetimeoffset requires 10 bytes of storage whereas datetime needs 8 bytes for precision 5 or greater, 7 bytes for precision 3-4, and 6 bytes for precision 2 or less.

SQL Server DateTime2(0) vs Date

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

System.DateTime and SQL Server DateTime2 Comparison Yielding Unexpected Result

There was a breaking change introduced in SQL Server 2016 that changed how DATETIME values are converted to DATETIME2 values, and because of this it's critical to always use DATETIME2 parameters when comparing to DATETIME2 columns.

Under database compatibility level 130, implicit conversions from
datetime to datetime2 data types show improved accuracy by accounting
for the fractional milliseconds, resulting in different converted
values. Use explicit casting to datetime2 datatype whenever a mixed
comparison scenario between datetime and datetime2 datatypes exists.
For more information, see this Microsoft Support Article.

Breaking changes to Database Engine features in SQL Server 2016

see also this blog

Essentially this is yet another reason to never use AddWithValue, as that sets the parameter type based on the .NET parameter value type, when it should always be set based on the SQL Server column type instead.

To fix just use a DATETIME2 parameter.

weirdCommand.Parameters.Add("@inputDate",System.Data.SqlDbType.DateTime2, 7).Value = found;


Related Topics



Leave a reply



Submit