Stealing time in SQL Server

Akos Nagy
Mar 20, 2017

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';
   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 :)

Akos Nagy
Posted in SQL Server