CODEWITHSUNDEEP
sqlsql-serverstringcharacterbetween
Abi rami
Abi rami

Reputation: 9

How are character values interpreted by between operators? (SQL)

I had a code in SQL which passes for

'2:00' between '10:00' and '22:00'

but fails for 

'02:00' between '10:00' and '22:00'

Can anyone help me understand how string values are interpreted by between operators and how the above given scenario has occurred?

Upvotes: 0

Views: 113

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

In your first BETWEEN clause, which passes erroneously:

'2:00' between '10:00' and '22:00'

The values are sorting lexicographically as text, not as numbers. Here they are sorted as text:

10:00
2:00
22:00

The text 22 is "greater" than 2:, so the check passes. The solution here is either to compare the times as numbers, or always ensure that the times have the same width. Assuming your data look like the first case, you may try casting to time in SQL Server:

SELECT *
FROM yourTable
WHERE CAST('2:00' AS time) BETWEEN '10:00' AND '22:00';

The above query will fail, as expected, because you are now comparing a proper time literal against a range of proper time literals.

Upvotes: 3

Related Questions