SQL查询datetime2时间范围时如何正确包含右边界

解决方案 #

最优方案（推荐，不影响索引性能） #

不要对CreatedAt字段做函数处理（会导致字段上的索引失效，数据量大时查询性能骤降），只需要调整右边界的对比逻辑即可：
你需要包含22/07/2021 07:01:00这一秒内的所有记录（无论毫秒位是多少），只需要把右边界的对比条件改为小于结束时间加1秒即可，SQL写法如下：

SELECT * FROM tableName 
WHERE CreatedAt >= @startDate 
AND CreatedAt < DATEADD(SECOND, 1, @endDate)

对应的Linq to SQL写法：

var query = db.TableName
    .Where(x => x.CreatedAt >= startDate && x.CreatedAt < endDate.AddSeconds(1));

这种写法的优势：

• 可以完整包含22/07/2021 07:01:00.000到22/07/2021 07:01:00.9999999的所有记录，刚好覆盖你需要的右边界范围
• 不会误包含22/07/2021 07:01:01及之后的错误数据
• CreatedAt字段没有被函数包裹，可以正常命中索引，查询性能最优

原写法问题说明 #

• 直接<= endDate无法匹配的原因：你传入的endDate如果是精确到秒的字符串，和datetime2类型对比时，数据库会默认补全毫秒为.0000000，只能匹配到毫秒位刚好为0的07:01:00记录，漏了同一秒内毫秒位不为0的记录
• 加1分钟的写法边界过大，会多拿后续59秒的错误数据
• 对CreatedAt用DATEADD截断毫秒的写法会破坏索引，仅适合小表临时查询，不推荐生产环境使用

内容的提问来源于stack exchange，提问作者Mellionis