SQL Server comparing dates algorithm
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.