如何在Server中设置最后一个业务日期
如何在Server中设置最后一个业务日期
提问于 2021-06-28 15:28:13
我是一个新的SQL学习者。我需要在查询中声明最后一个营业日期。例如,
- 如果今天是星期一，那么在上星期五返回。
- ，如果今天是从星期二到星期五，那么昨天回来。
周末和国庆节必须排除在外。
我需要这种格式：
declare @LastBizDate as datetime
set @LastBizDate = (DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())
WHEN 'Saturday' THEN -1
WHEN 'Sunday' THEN -2
ELSE -1 END, DATEDIFF(DAY, 0, GETDATE())))我真的很感激你的帮助！
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-06-28 15:55:10
declare @LastBizDate as datetime
set @LastBizDate = CASE DATENAME(WEEKDAY, GETDATE())
WHEN 'Monday' THEN GETDATE()-3
WHEN 'Sunday' THEN GETDATE()-2
ELSE GETDATE()-1 END
PRINT @LastBizDateStack Overflow用户
发布于 2021-06-28 16:04:48
假设NationalHolidayIndicator或NationalHoliday已经在您的日期表中，则使用关联子查询。
SELECT d.[Date]
, d.NationalHolidayIndicator
--, d.NationalHoliday
, (SELECT MAX([Date])
FROM [Date]
WHERE [Date] < d.[Date]
AND DATENAME(WEEKDAY, d.[Date]) NOT IN ('Saturday', 'Sunday')
AND d.NationalHolidayIndicator = 'no'
--AND d.NationalHoliday IS NULL
) as PreviousBusinessDay
FROM [Date] d如果您还没有NationalHolidayIndicator和NationalHoliday，那么美国的情况可能如下所示：
case
when datepart(month, dt) = 1 and datepart(day, dt) = 1 and datepart(weekday, dt) between 2 and 6 then 'yes'
when datepart(month, dt) = 1 and datepart(day, dt) = 2 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 12 and datepart(day, dt) = 31 and datepart(weekday, dt) = 6 then 'yes'
when datepart(month, dt) = 1 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 and datepart(year, dt) > 1985 then 'yes'
when datepart(month, dt) = 2 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 5 and datepart(day, dt) > 24 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 7 and datepart(day, dt) = 4 and datepart(weekday, dt) between 2 and 6 then 'yes'
when datepart(month, dt) = 7 and datepart(day, dt) = 5 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 7 and datepart(day, dt) = 3 and datepart(weekday, dt) = 6 then 'yes'
when datepart(month, dt) = 9 and datepart(day, dt) between 1 and 7 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 10 and datepart(day, dt) between 8 and 14 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 11 and datepart(day, dt) = 11 and datepart(weekday, dt) between 2 and 6 then 'yes'
when datepart(month, dt) = 11 and datepart(day, dt) = 12 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 11 and datepart(day, dt) = 10 and datepart(weekday, dt) = 6 then 'yes'
when datepart(month, dt) = 11 and datepart(day, dt) between 22 and 28 and datepart(weekday, dt) = 5 then 'yes'
when datepart(month, dt) = 12 and datepart(day, dt) = 25 and datepart(weekday, dt) between 2 and 6 then 'yes'
when datepart(month, dt) = 12 and datepart(day, dt) = 26 and datepart(weekday, dt) = 2 then 'yes'
when datepart(month, dt) = 12 and datepart(day, dt) = 24 and datepart(weekday, dt) = 6 then 'yes'
else 'no'
end as NationalHolidayIndicator
, case
when datepart(month, dt) = 1 and datepart(day, dt) = 1 then 'New Year''s Day'
when datepart(month, dt) = 1 and datepart(day, dt) = 2 and datepart(weekday, dt) = 2 then 'New Year''s Day - legal'
when datepart(month, dt) = 12 and datepart(day, dt) = 31 and datepart(weekday, dt) = 6 then 'New Year''s Day - legal'
when datepart(month, dt) = 1 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 and datepart(year, dt) > 1985 then 'Martin Luther King Day'
when datepart(month, dt) = 2 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 then 'Presidents'' Day'
when datepart(month, dt) = 5 and datepart(day, dt) > 24 and datepart(weekday, dt) = 2 then 'Memorial Day'
when datepart(month, dt) = 7 and datepart(day, dt) = 4 then 'Independence Day'
when datepart(month, dt) = 7 and datepart(day, dt) = 5 and datepart(weekday, dt) = 2 then 'Independence Day - legal'
when datepart(month, dt) = 7 and datepart(day, dt) = 3 and datepart(weekday, dt) = 6 then 'Independence Day - legal'
when datepart(month, dt) = 9 and datepart(day, dt) between 1 and 7 and datepart(weekday, dt) = 2 then 'Labor Day'
when datepart(month, dt) = 10 and datepart(day, dt) between 8 and 14 and datepart(weekday, dt) = 2 then 'Columbus Day'
when datepart(month, dt) = 11 and datepart(day, dt) = 11 then 'Veterans Day'
when datepart(month, dt) = 11 and datepart(day, dt) = 12 and datepart(weekday, dt) = 2 then 'Veterans Day - legal'
when datepart(month, dt) = 11 and datepart(day, dt) = 10 and datepart(weekday, dt) = 6 then 'Veterans Day - legal'
when datepart(month, dt) = 11 and datepart(day, dt) between 22 and 28 and datepart(weekday, dt) = 5 then 'Thanksgiving Day'
when datepart(month, dt) = 11 and datepart(day, dateadd(day, -1, dt)) between 22 and 28 and datepart(weekday, dateadd(day, -1, dt)) = 5 and datepart(year, dt) between 1977 and 2013 then 'Thanksgiving, Day After'
when datepart(month, dt) = 11 and datepart(day, dateadd(day, -1, dt)) between 22 and 28 and datepart(weekday, dateadd(day, -1, dt)) = 5 and datepart(year, dt) > 2014 then 'Native American Heritage Day'
when datepart(month, dt) = 12 and datepart(day, dt) = 25 then 'Christmas Day'
when datepart(month, dt) = 12 and datepart(day, dt) = 26 and datepart(weekday, dt) = 2 then 'Christmas Day - legal'
when datepart(month, dt) = 12 and datepart(day, dt) = 24 and datepart(weekday, dt) = 6 then 'Christmas Day - legal'
end as NationalHoliday...although我的假期名称和日期来自我的州法律，而不是联邦法律。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68166087
复制相关文章
相似问题
腾讯云开发者
