我被要求完成一个T-SQL挑战,需要根据以下日期位置获取下个月的等效日期:
今天是2011年7月18日,换句话说,这个月是第三个星期一...
现在我需要在SQL Server查询中获取下个月的第三个星期一(2011年8月15日)。
这就像谷歌日历的重复规则。
我尝试了很多公式,但变得非常复杂,希望能得到任何帮助。
我被要求完成一个T-SQL挑战,需要根据以下日期位置获取下个月的等效日期:
今天是2011年7月18日,换句话说,这个月是第三个星期一...
现在我需要在SQL Server查询中获取下个月的第三个星期一(2011年8月15日)。
这就像谷歌日历的重复规则。
我尝试了很多公式,但变得非常复杂,希望能得到任何帮助。
这个语法将计算下一周的同一天,并在不存在时放入 null。
declare @t datetime
set @t = '2008-01-29'
select case (datepart(day, @t)+ 6) / 7
when (datepart(day, @t + 28)+ 6) / 7 then @t + 28
when (datepart(day, @t + 35)+ 6) / 7 then @t + 35
else null end
CREATE FUNCTION fnSameDayOfWeekNextMonth (@Date datetime)
RETURNS datetime
AS BEGIN
RETURN (
SELECT
DATEADD(WEEK,
CASE (MONTH(ApproxDate) - MONTH(@Date) + 12) % 12
WHEN 2 THEN -1
ELSE ThisDayNum - (DAY(ApproxDate) - 1) / 7
END,
ApproxDate)
FROM (
SELECT
ThisDayNum = (DAY(@Date) - 1) / 7,
ApproxDate = DATEADD(WEEK, 5, @Date)
) s
)
END
将给定日期加上5周,得到一个大致日期。
获取给定日期所在月份中该星期的天数。
如果大致日期的月份比给定日期的月份晚2个月,则从大致日期中减去1周并返回结果。
否则,获取与#2相同的内容,但是针对大致日期(而不是给定日期)。
获取#2和#4之间的差异。
将#5作为周数从大致日期中减去并返回结果。
…WHEN 2 THEN -1…
部分更改为…WHEN 2 THEN NULL…
。幸运的是,我曾经做过这件事:http://blog.prokrams.com/2007/06/18/determining-the-ordinal-of-a-weekday/
Create Function fn_ReturnOrdinalDay( @TestDate datetime) returns int
begin
declare@ordinal int,@loopdate datetime
set @loopdate = @TestDateset
set @ordinal = 0
while datepart(m, @TestDate) = datepart(m, @loopdate)
begin
set @ordinal = @ordinal + 1
set @testdate = dateadd(d, -7, @testdate)
end
return @ordinal
end
预计完成时间:要找到与今天相同序数的下一天,只需将今天的日期加上+7,直到fn_ReturnOrdinalDay返回与今天相同的结果即可。
select
min(BaseDate)
from
dbo.Calendar
where
DayDescription = (select DayDescription from dbo.Calendar where BaseDate = @Today) and
BaseDate > @Today
这可能会对某些人有所帮助,如果您想要获取任意月数后的序数日期,我已经结合了所有建议创建了这个函数:
CREATE FUNCTION [dbo].[fncSameDayOfWeekAnotherMonth] (@Date datetime, @Months int)
RETURNS datetime
AS BEGIN
declare @FONM datetime, @day TinyInt, @ordinal tinyint, @FDM datetime, @result datetime
Set @FONM = DATEADD(dd,-(DAY(DATEADD(mm,@Months,@Date))-1),DATEADD(mm,@Months,@Date))--first day of month
Set @day = datepart(weekday,@Date)+7
set @ordinal=ceiling( cast(datepart(day,@Date)as float)/7 )--ordinal day
set @FDM = DateAdd(day, ((@day -DatePart(weekday, @FONM))%7), @FONM)--first day of week (monday, friday, etc) on month
set @result = DateAdd(day, (@ordinal-1)*7, @FDM)
RETURN case when DATEDIFF(month, @Date, @result) > @Months then DateAdd(day, (@ordinal-2)*7, @FDM) else @result end --if result exceeds number of months just subtract one week from ordinal
END
它接收日期和月份数量,获取该天的序号和您想要的月份的第一天。通过最后一个来查找该月中星期几的第一次出现,然后计算从您的日期开始的任何月份的相应序数日期。
我知道它有很多代码,可能需要改进,但至少它运行得非常好。
@Date
是其月份中某个工作日的第5次出现,并且计算出的月份中对应的日期未定义,则您的函数返回同一工作日的第4次出现,而不是NULL。但我理解这是您对这种情况的特殊偏好。除此之外,这个函数似乎工作良好,只是在年度过渡时会失败。我认为可以通过使用DATEDIFF(mm, …) > @Month
而不是MONTH(…) > MONTH(…) + @Month
来修复这个问题。 - Andriy MSELECT dbo.fncSameDayOfWeekAnotherMonth('20111129', 3)
。我应该说,在年份转换的某些情况下会失败。这些“某些情况”是指当日期是工作日#5,并且您指定的月份将比原始月份少,并且没有那个工作日#5时。这就是为什么DATEDIFF会更可靠的原因。 - Andriy M如果你只需要完成上面的挑战,那么这相对来说比较容易。
使用日期格式函数(我记不清确切名称)找出当天的名称。
从该月份开始循环,并在到达输入日期之前计算出你遇到的例如星期二等工作日的数量。
然后循环下一个月(或任何月份)x次,在达到所需数量后,取下一个星期二。
-- 对于每月的第三个星期三
DATEPART(dw, PromptDate) + DATEPART(dw, PromptDate - 7) + DATEPART(dw, PromptDate - 14) = 12
-- 我们只关心每月的第三个星期三,不应该是第四或第五个星期三。
AND DATEDIFF(MONTH, PromptDate - 14, PromptDate - 21) <> 0