Why does the TSQL BETWEEN operator include the first and last value in the results when filtering on numbers, but only includes the first value and not the last for dates?

Are you sure those are dates and not datetime type instead? On datetime it defaults to 12 AM and will not include any values past that time. In this case 12 AM means midnight, with is confusing for me, but that's USA for you.

Read last paragraph: https://docs.microsoft.com/en-us/...

This will not return row:

SELECT 1 WHERE CONVERT(datetime, '2002-01-05 00:01:00') BETWEEN '20011212' AND '20020105';

btw. On PostgreSQL it defaults to 00:00:00 as well, so anything past midnight is not included.

returns false:

SELECT '2019-02-15 00:01:00'::timestamp BETWEEN '2019-02-14'::date AND '2019-02-15'::date

@arraysstartat1 nice catch, happens to me sometimes when filtering in LINQ and always takes me a while to notice it lol

That's why I'll just keep using the normal operators ánd define the time..