SQL Server 2016+如何更简洁生成datetime分钟级时间范围

SQL Server 2016 或更高版本

传入一个datetime类型的日期时间值，基于其小时、分钟维度生成对应的分钟级时间区间。
例如传入值为'6/11/2022 7:14:47 PM'时，期望得到的筛选区间为criteria_date介于'6/11/2022 19:14:00'与'6/11/2022 19:15:00'之间。

原写法通过转换字符串截取时分的方式拼接起止时间，代码如下：

DECLARE @date_in DATETIME = '6/11/2022 7:14:47 PM'

-- Goal is to come up with a date range of the minute
-- In the above example:
-- ...where criteria_date between '6/11/2022 19:14:00' and '6/11/2022 19:15:00'

DECLARE @date_from DATETIME
DECLARE @date_to DATETIME
DECLARE @hourMinutesFrom VARCHAR(5)
DECLARE @hourMinutesTo VARCHAR(5)

SELECT @hourMinutesFrom = CONVERT(VARCHAR(5),@date_in,108) 
SELECT @hourMinutesTo = CONVERT(VARCHAR(5),DATEADD(MINUTE,1,@date_in),108) 

SELECT @date_from = DATEADD(day, DATEDIFF(day, 0, @date_in), @hourMinutesFrom)
SELECT @date_to = DATEADD(DAY, DATEDIFF(DAY, 0, @date_in), @hourMinutesTo)

SELECT @date_from
SELECT @date_to

完全不需要做字符串类型转换，直接利用SQL Server的日期计算逻辑即可实现，代码更短、执行效率更高：

DECLARE @date_in DATETIME = '6/11/2022 7:14:47 PM'

DECLARE @date_from DATETIME = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date_in), 0)
DECLARE @date_to DATETIME = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date_in) + 1, 0)

SELECT @date_from, @date_to

写法逻辑说明 #

• DATEDIFF(MINUTE, 0, @date_in)会计算SQL Server时间基准点（1900-01-01 00:00:00）到传入时间的总分钟数，计算时会自动舍弃秒、毫秒部分的差值
• 把计算得到的总分钟数通过DATEADD加回基准时间点，就能得到截断到分钟的区间起始值
• 总分钟数+1后再加回基准时间点，就是区间的结束值

实际查询使用建议 #

注意：datetime类型精度为3.33毫秒，datetime2精度更高，如果用BETWEEN写区间闭合条件，很容易漏掉结束时间点前几毫秒的数据，半开区间写法是时间筛选的通用最佳实践。

做时间范围筛选时，推荐直接用半开区间写法，避免精度问题导致漏数：

SELECT * FROM 你的业务表
WHERE criteria_date >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date_in), 0)
  AND criteria_date < DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date_in) + 1, 0)

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