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
anddatetimeoffset
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
MySQL: @Variable Vs. Variable. What's the Difference
How to Update Only One Field Using Entity Framework
Conditional Where Clause in SQL Server
Get a Comma Delimited String from Rows
Difference Between Having and Where in Sql
How to Create Multiple One to One's
How to Check If a Table Exists in a Given Schema
Not Equal ≪≫ != Operator on Null
Computed/Calculated/Virtual/Derived Columns in Postgresql
How to Access the "Previous Row" Value in a Select Statement
How to Select from Stored Procedure
What This Query Does to Create Comma Delimited List SQL Server