如何在每个月的列中获取最多周六日期,而无需硬编码
如何在SQL Server的每个月的列中获取最大星期六日期。有人能帮帮我吗?
现在我只需要上月最后一个星期六的日期。 例如,
该表具有
07-08-2021 - Saturday
14-08-2021 - Saturday
21-08-2021 - Saturday
28-08-2021 - Saturday
04-09-2021 - Saturday
11-09-2021 - Saturday
18-09-2021 - Saturday
25-09-2021 - Saturday
假设我们在8月份,我需要选择该月的最后一个星期六(只有28-08-2021) 假设我们在9月份,我需要选择该月的最后一个星期六(仅25-09-2021)
输出:
28-08-2021
25-09-2021
解决方案
以下是计算第N个工作日的函数-可以从月初或月底计算。
Alter Function dbo.fnGetNthWeekDay (
@theDate datetime
, @theWeekday int
, @theNthDay int
)
Returns Table
As
Return
/*
Adapted from a version published by Peter Larrson - with minor modifications for performance
and restructured to eliminate usage of a derived table.
Inputs:
@theDate any date in the month
@theWeekday the weekday to calculate: 1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday
@theNthDay the week count where positive is from beginning of the month
and negative is from end of month
Outputs:
@theDate the date entered
@theNthDate the Nth date of the month
*/
Select theDate = @theDate
, dt.nthDate
From (Values (dateadd(month, datediff(month, @theNthDay, @theDate), 0))) As mm(FirstOfMonth)
Cross Apply (Values (dateadd(day, 7 * @theNthDay - 7 * sign(@theNthDay + 1)
+ (@theWeekday + 6 - datediff(day, -53690, mm.FirstOfMonth) % 7) % 7, mm.FirstOfMonth))) As dt(nthDate)
Where @theWeekday Between 1 And 7
And datediff(month, dt.nthDate, @theDate) = 0
And @theNthDay In (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5);
Go
然后您可以这样称呼它:
Select * From dbo.fnGetNthWeekDay('2021-08-15', 6, -1) nwd
相关文章