How does SQL Server handle a comparison between dates when both are strings?-CodePudding

I am just curious and wanted to understand how the following situation is handled by SQL Server. I am comparing two dates, but both dates are in a string format.

Let's say date is string, that contains a date in the format of YYYY-MM-DD.

Something like shown below actually works and would give me all entries with year < 2001. Are both sides cast to DATE implicitly?

SELECT *
FROM X
WHERE (date < '2001')

But when you compare date with an arbitrary string like so:

SELECT * 
FROM X
WHERE (date < 'abcdefg')

the results don't make any sense. It does not throw an exception and if one is using '<' all the tuples are returned and if one uses '>' none of them are returned, independent of what string I put on the right side of the expression.

Secondly, I know that from a performance perspective string comparisons are very bad and that comparisons between proper date-types are much much faster. Would it be worth it to cast the entire date column to a proper format before making such a query, or does it not matter because SQL Server will make implicit casts anyways?

Could somebody explain to me how this works in the background? Thank you!

CodePudding user response：

SQL won't do any "implicit" cast because it doesn't know that your strings are to be interpreted as dates: how could it know? So the comparison is between strings and the result will be depending on the collation you set for your DB. This usually means, digits come before letters: so, yes, "2021" is lower than any (letter based) strings