背景
我们的服务合同可以在任意日期开始和结束。然而,合同通常以“X 年减一天”结束。例如,典型的日期范围:
- 开始日期 = 2018-01-08,结束日期 = 2023-01-07
- 开始日期 = 2021-06-01,结束日期 = 2023-05-31
然而,有些合同的日期范围非常任意。有些非常短,但也可能跨越几年:
- 开始日期 = 2024-04-30,结束日期 = 2024-07-16
- 开始日期 = 2024-10-07,结束日期 = 2024-10-30
- 开始日期 = 2021-03-15,结束日期 = 2025-12-25
要求
计算合同跨度的月数,以确定在合同有效期内将创建多少张每月发票。假设发票是在该月的最后一天生成的,因此开始日期为 4 月 15 日的合同将在 4 月 30 日生成第一张发票,即“四月发票”。
问题
仅使用 DATEDIFF(MONTH, @Date1, @Date2) 产生的结果并不总是符合要求。
当我在下面说“产生不正确的结果”时,我并不是说该
DATEDIFF()
函数是错误的,我是说这不是我的要求所需的结果。
PRINT DATEDIFF(MONTH, '2018-01-08', '2023-01-07')
产生 60 个月的正确结果。PRINT DATEDIFF(MONTH, '2021-06-01', '2023-05-31')
产生 23 个月的错误结果,而我需要的结果是 24 个月。PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')
产生错误结果 0,而我需要结果 1。PRINT DATEDIFF(MONTH, '2024-04-15', '2024-07-16')
产生错误结果 3,而我需要结果 4PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')
产生错误结果 0,而我需要结果 1。PRINT DATEDIFF(MONTH, '2024-09-15', '2024-10-15')
产生错误结果 1,而我需要结果 2。
所以,我不能只给出所有情况的结果+ 1
。DATEDIFF()
当合同在同年同月开始和结束时,DATEDIFF()
会产生一个 in
目前的解决方案
这是我当前的解决方案:
PRINT CASE
WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
-- Special case when a contract starts and ends in the same month and year.
THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
WHEN MONTH(@StartDate) = MONTH(@EndDate)
THEN DATEDIFF(MONTH, @StartDate, @EndDate)
ELSE
DATEDIFF(MONTH, @StartDate, @EndDate) + 1
END
这是我当前的解决方案,采用不同日期范围的 5 次测试的形式:
DECLARE @StartDate AS DATE = '2018-01-08'
DECLARE @EndDate AS DATE = '2023-01-07'
PRINT '-------------------------------------------------------'
PRINT 'Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07'
PRINT '-------------------------------------------------------'
PRINT ''
IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
MONTH(@StartDate) = MONTH(@EndDate)
PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'
PRINT CASE
WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
-- Special case when a contract starts and ends in the same month and year.
THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
WHEN MONTH(@StartDate) = MONTH(@EndDate)
THEN DATEDIFF(MONTH, @StartDate, @EndDate)
ELSE
DATEDIFF(MONTH, @StartDate, @EndDate) + 1
END
PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31'
PRINT '-------------------------------------------------------'
PRINT ''
SET @StartDate = '2021-06-01'
SET @EndDate = '2023-05-31'
IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
MONTH(@StartDate) = MONTH(@EndDate)
PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'
PRINT CASE
WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
-- Special case when a contract starts and ends in the same month and year.
THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
WHEN MONTH(@StartDate) = MONTH(@EndDate)
THEN DATEDIFF(MONTH, @StartDate, @EndDate)
ELSE
DATEDIFF(MONTH, @StartDate, @EndDate) + 1
END
PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16'
PRINT '-------------------------------------------------------'
PRINT ''
SET @StartDate = '2024-04-30'
SET @EndDate = '2024-07-16'
IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
MONTH(@StartDate) = MONTH(@EndDate)
PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'
PRINT CASE
WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
-- Special case when a contract starts and ends in the same month and year.
THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
WHEN MONTH(@StartDate) = MONTH(@EndDate)
THEN DATEDIFF(MONTH, @StartDate, @EndDate)
ELSE
DATEDIFF(MONTH, @StartDate, @EndDate) + 1
END
PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30'
PRINT '-------------------------------------------------------'
PRINT ''
SET @StartDate = '2024-10-07'
SET @EndDate = '2024-10-30'
IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
MONTH(@StartDate) = MONTH(@EndDate)
PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'
PRINT CASE
WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
-- Special case when a contract starts and ends in the same month and year.
THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
WHEN MONTH(@StartDate) = MONTH(@EndDate)
THEN DATEDIFF(MONTH, @StartDate, @EndDate)
ELSE
DATEDIFF(MONTH, @StartDate, @EndDate) + 1
END
PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15'
PRINT '-------------------------------------------------------'
PRINT ''
SET @StartDate = '2024-09-15'
SET @EndDate = '2024-10-15'
IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
MONTH(@StartDate) = MONTH(@EndDate)
PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'
PRINT CASE
WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
-- Special case when a contract starts and ends in the same month and year.
THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
WHEN MONTH(@StartDate) = MONTH(@EndDate)
THEN DATEDIFF(MONTH, @StartDate, @EndDate)
ELSE
DATEDIFF(MONTH, @StartDate, @EndDate) + 1
END
结果:
-------------------------------------------------------
Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07
-------------------------------------------------------
The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.
60
-------------------------------------------------------
Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
24
-------------------------------------------------------
Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
4
-------------------------------------------------------
Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30
-------------------------------------------------------
The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.
1
-------------------------------------------------------
Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
2
对于 5 组测试日期范围,结果确实符合我的要求。
问题
除了我笨拙的陈述之外,还有更可靠的方法吗CASE
?当然,我不会在这里重新发明轮子。我确信还有更多日期范围的边缘情况我尚未考虑进行测试。我这样做很艰难吗?