如果我有@dayOfMonth
介于 1 到 31 之间的整数值 ( ),我将如何确定本月剩余时间或下个月的下一个日期?
如果@dayOfMonth
大于当月的有效天数,则结果应为当月的最后一天。例如,如果@dayOfMonth
是 31,而今天是 4/15/17,我希望结果是 4/30/17。
这些是我对以下值的期望结果:
@testDate = 4/15/17 and @dayOfMonth = 31, result should be 4/30/17
@testDate = 4/15/17 and @dayOfMonth = 30, result should be 4/30/17
@testDate = 4/15/17 and @dayOfMonth = 25, result should be 4/25/17
@testDate = 4/15/17 and @dayOfMonth = 7, result should be 5/7/17
@testDate = 4/15/17 and @dayOfMonth = 15, result should be 5/15/17
@testDate = 1/31/17 and @dayOfMonth = 31, result should be 2/28/17
@testDate = 12/25/15 and @dayOfMonth = 5, result should be 1/5/18
我认为这说明了与本月相关的所有情况。似乎罗嗦,但它给出了预期的结果。注:这两个EOMONTH
和DATEFROMPARTS
至少需要SQL 2012。
DECLARE @testdate date
SET @testdate = '4/15/17'
DECLARE @dayOfMonth int
SET @dayOfMonth = 31
SELECT CASE WHEN @dayOfMonth > DAY(@testdate) AND @dayOfMonth > DAY(EOMONTH(@testdate)) THEN EOMONTH(@testDate)
WHEN @dayOfMonth > DAY(@testdate) AND @dayOfMonth <= DAY(EOMONTH(@testdate)) THEN DATEFROMPARTS(YEAR(@testDate),MONTH(@testDate),@dayOfMonth)
WHEN @dayOfMonth <= DAY(@testdate) AND @dayOfMonth > DAY(DATEADD(MONTH,1,EOMONTH(@testdate))) THEN DATEADD(MONTH,1,EOMONTH(@testdate))
WHEN @dayOfMonth <= DAY(@testdate) AND @dayOfMonth <= DAY(DATEADD(MONTH,1,EOMONTH(@testdate))) THEN DATEFROMPARTS(YEAR(DATEADD(MONTH,1,@testDate)),MONTH(DATEADD(MONTH,1,@testDate)),@dayOfMonth)
END AS NextDisbursementDate
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句