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

Consider the following query for example:

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 ?

  • 86400 thousands. Exactly. Or 86400 million microseconds, available in recent versions (datetime2). – ypercubeᵀᴹ Jan 18 '16 at 20:31
  • So in fact it is making a numeric comparision with all possible values??? This sounds insane! – Dimitar Tsonev Jan 18 '16 at 20:32
  • 3
    Why do you think it has to compare all possible values? Do you compare 7 and 7000000000 by counting all the way from 7 to 7000000000? – ypercubeᵀᴹ Jan 18 '16 at 20:35
  • I don't think it compares it. I just want to know what's the internal representation of the datetime and how it does the comparision. – Dimitar Tsonev Jan 18 '16 at 20:37
  • 4
    The internal representation of a datetime is two integers - number of days since 1900-01-01 and number of ticks since midnight. Also, implicit conversion of a constant or parameter is not anything to be worried about. If the conversion were happening on the column, then you'd have reason to worry. How the comparison is done internally is not documented AFAIK, and even if it were (or you somehow discovered it), what would you do with that information? – Aaron Bertrand Jan 18 '16 at 21:40

Assuming DateTimeValue is a DATETIME column or similar then both '2016-01-01 00:00:000' and '2016-01-03 00:00:000' are going to get cast to that type and the comparison is done using the natural date comparison routine. Of course if you have something odd like dates stored as strings then yes it may perform a string comparison.

Internally DATETIME is essentially a large integer type (it is actually implemented as two integers but for the purpose of this discussion that difference is purely academic) so the natural comparison is integer comparison.

The conversion of your input strings to DATETIME is not going to take significant resources: it will be done once for the whole query and not for every row that gets considered.

If you have a relevant index for DateTimeValue then a ranged query will be used on that index (a seek to 2016-01-01 or the first value above that then a scan down the index until the values seen stop being less than 2016-01-03. This should be fairly efficient though you will of course have heap/cluster lookups going on if that index isn't your clustered one or covers all the columns. If you look at the query plan for such a query in SSMS you should see that index seek and related row lookups clearly.

So it is an integer comparison, but the other way around to the one you call ridiculous (which you are right, would be silly). There will be a few integer comparisons as it works down the B-Tree of the index to find the first relevant row followed by one comparison per subsequent row as it asks "have we hit 2016-01-03 or higher yet?", not one comparison for every possible value in the range. It might help to think of how you would go about following the instruction "list all the words from your dictionary that are between index and iteration" - that is essentially what SQL Server is doing with the datetime range search.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.