Stealing time in SQL Server
Here's a question for you: what is the output of this simple script in T-SQL if I run it on SQL Server 2016 Developer Edition (or any other edition and any other version, for that matter):
declare @t1 datetime='2017-03-20 12:46:37.000'; declare @t2 datetime='2017-03-20 12:46:37.001'; if @t1<@t2 print 'Smaller'; else if @t1>@t2 print 'Greater'; else print 'Equal';
Even though the datetimes are clearly different (and @t1 is smaller than @t2), the result is 'Equal'. Why? Because the datetime type has a theoretical precision of 3.33 ms (check out the documentation). And the different between the two is clearly less, so it gets lost when you store the value.
So if you have problems like these, use datetime2. It has a theoretical precision of 100 ns (check the documentation).
That's all for today :)
Posted in SQL Server