CODEWITHSUNDEEP
sql-serverdatetimecompare
Dimitar Tsonev
Dimitar Tsonev

Reputation: 3892

SQL Server comparing dates algorithm

How is the comparison between dates (with times) actually implemented?

Consider the following query for example:

SELECT * 
FROM dbo.Table1 
WHERE DateTimeValue >= '2016-01-01 00:00:000' 
  AND DateTimeValue < '2016-01-03 00:00:000'

The above internal contains exact two days, no millisecond more or less.

So, how does SQL Server compare the values in the column with the values in the interval?

String comparison?

This seems not so bad, but if you see the execution plan, you'll notice that the SQL Server is using CONVERT_IMPLICIT to cast the string value to datetime.

Integer comparison?

This sounds ridiculous if you think how many milliseconds there are in one day.

Does anyone know how the comparison is implemented internally in SQL Server?

Upvotes: 0

Views: 113

Answers (1)

Tom H
Tom H

Reputation: 47402

Assuming that it hasn't changed in the last few years, SQL Server stores DATETIME values as two integers, one for the date portion and one for the time portion. Comparison of two datetime values is then just a simple comparison of two integers (something that computers are very good at doing.)

When it compares two datetime values with each other, it doesn't have to actually check the value against every possible millisecond in a day individually. It's a simple less than/greater than comparison. If I asked you to compare whether or not 5 was less than 10 then you wouldn't ask, "Is 5 equal to 6? Is 5 equal to 7? Is 5 equal to 8?..."

Upvotes: 1

Related Questions