SQL Server 报表场景如何获取上月/上季度精确到秒的日期范围

我们在为客户编写SQL报表查询时，需要根据客户协议按月、按季度或每半年生成自定义SQL报表，因此需要在报表运行时动态计算变量@startDate和@endDate的取值，精度要精确到秒级，避免出现数据遗漏。

统计周期示例 #

上月报表日期范围 #

@startDate: 2021-11-01 00:00:00.000
@endDate: 2021-11-30 23:59:59.000

上季度报表日期范围 #

@startDate: 2021-07-01 00:00:00.000
@endDate: 2021-09-30 23:59:59.000

主流数据库实现代码 #

SQL Server 版本 #

上月周期计算

DECLARE @startDate DATETIME, @endDate DATETIME
-- 取上月第一天0点
SET @startDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
-- 取当月第一天0点减3毫秒（SQL Server DATETIME精度为3毫秒，刚好落到上月最后一秒）
SET @endDate = DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

上季度周期计算

DECLARE @startDate DATETIME, @endDate DATETIME
SET @startDate = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) - 1, 0)
SET @endDate = DATEADD(MILLISECOND, -3, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))

上半年度周期计算

DECLARE @startDate DATETIME, @endDate DATETIME
DECLARE @CurrentHalfStart DATETIME = CASE WHEN DATEPART(QUARTER, GETDATE()) IN (1,2) 
                                        THEN DATEFROMPARTS(YEAR(GETDATE()),1,1)
                                        ELSE DATEFROMPARTS(YEAR(GETDATE()),7,1) END
SET @startDate = DATEADD(MONTH, -6, @CurrentHalfStart)
SET @endDate = DATEADD(MILLISECOND, -3, @CurrentHalfStart)

MySQL 版本 #

上月周期计算

SET @startDate = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00');
SET @endDate = DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00'), INTERVAL 1 SECOND);

上季度周期计算

SET @startDate = DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL (QUARTER(CURDATE())-2)*3 MONTH);
SET @endDate = DATE_SUB(DATE_ADD(@startDate, INTERVAL 3 MONTH), INTERVAL 1 SECOND);

优化建议：如果可以修改查询条件，更推荐使用半开区间写法：业务日期 >= @startDate AND 业务日期 < @nextPeriodStart，其中@nextPeriodStart是统计周期结束后的第一个时间点（比如上月统计就取当月1号0点），这种写法完全不需要处理秒级精度问题，兼容所有日期精度类型，出错概率远低于计算最后一秒的写法。

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