SQL 如何使用sql server获取当前工作日的一周内的上一个工作日
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9922756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get Previous business day in a week with that of current Business Day using sql server
提问by 0537
i have an ssis Package which runs on business days (mon-Fri). if i receive file on tuesday , background(DB), it takes previous business day date and does some transactions. If i run the job on friday, it has to fetch mondays date and process the transactions.
我有一个在工作日（周一至周五）运行的 ssis 包。如果我在星期二收到文件，背景（DB），它需要前一个工作日的日期并进行一些交易。如果我在星期五运行这项工作，它必须获取星期一的日期并处理交易。
i have used the below query to get previous business date
我使用以下查询来获取上一个营业日期
Select Convert(varchar(50), Position_ID) as Position_ID,
TransAmount_Base,
Insert_Date as InsertDate
from tblsample
Where AsOfdate = Dateadd(dd, -1, Convert(datetime, Convert(varchar(10), '03/28/2012', 101), 120))
Order By Position_ID
if i execute this query i'll get the results of yesterdays Transactios. if i ran the same query on monday, it has to fetch the Fridays transactions instead of Sundays.
如果我执行这个查询，我会得到昨天 Transactios 的结果。如果我在星期一运行相同的查询，它必须获取星期五的交易而不是星期日。
回答by GarethD
SELECT DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())
WHEN 'Sunday' THEN -2
WHEN 'Monday' THEN -3
ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))
I prefer to use DATENAMEfor things like this over DATEPARTas it removes the need for Setting DATEFIRSTAnd ensures that variations on time/date settings on local machines do not affect the results. Finally DATEDIFF(DAY, 0, GETDATE())will remove the time part of GETDATE()removing the need to convert to varchar (much slower).
我更喜欢使用DATENAME这种方式，DATEPART因为它不需要设置DATEFIRST并确保本地机器上时间/日期设置的变化不会影响结果。最后DATEDIFF(DAY, 0, GETDATE())将去除时间部分GETDATE()去除需要转换为varchar（慢得多）。
EDIT (almost 2 years on)
编辑（近 2 年）
This answer was very early in my SO career and it annoys me everytime it gets upvoted because I no longer agree with the sentiment of using DATENAME.
这个答案在我的 SO 职业生涯中很早就出现了，每次它被点赞时都会让我很恼火，因为我不再同意使用 DATENAME 的观点。
A much more rubust solution would be:
一个更可靠的解决方案是：
SELECT DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7
WHEN 1 THEN -2
WHEN 2 THEN -3
ELSE -1
END, DATEDIFF(DAY, 0, GETDATE()));
This will work for all language and DATEFIRST settings.
这将适用于所有语言和 DATEFIRST 设置。
回答by noworries
Then how about:
那么怎么样：
declare @dt datetime='1 dec 2012'
select case when 8-@@DATEFIRST=DATEPART(dw,@dt)
then DATEADD(d,-2,@dt)
when (9-@@DATEFIRST)%7=DATEPART(dw,@dt)%7
then DATEADD(d,-3,@dt)
else DATEADD(d,-1,@dt)
end
回答by Dale Kilian
This function returns last working day and takes into account holidays and weekends. You will need to create a simple holiday table.
此函数返回最后一个工作日并考虑假期和周末。您将需要创建一个简单的假日表。
-- =============================================
-- Author: Dale Kilian
-- Create date: 2019-04-29
-- Description: recursive function returns last work day for weekends and
-- holidays
-- =============================================
ALTER FUNCTION dbo.fnGetWorkWeekday
(
@theDate DATE
)
RETURNS DATE
AS
BEGIN
DECLARE @importDate DATE = @theDate
DECLARE @returnDate DATE
--Holidays
IF EXISTS(SELECT 1 FROM dbo.Holidays WHERE isDeleted = 0 AND @theDate = Holiday_Date)
BEGIN
SET @importDate = DATEADD(DAY,-1,@theDate);
SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Satruday
IF(DATEPART(WEEKDAY,@theDate) = 7)
BEGIN
SET @importDate = DATEADD(DAY,-1,@theDate);
SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Sunday
IF(DATEPART(WEEKDAY,@theDate) = 1)
BEGIN
SET @importDate = DATEADD(DAY,-2,@theDate);
SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
RETURN @importDate;
END
GO
回答by SimpleMan
You can easily make this a function call, adding a second param to replace GetDate() with whatever date you wanted. It will work for any day of the week, at any date range, if you change GetDate(). It will not change the date if the day of week is the input date (GetDate())
您可以轻松地将其设为函数调用，添加第二个参数以将 GetDate() 替换为您想要的任何日期。如果您更改 GetDate()，它将适用于一周中的任何一天，在任何日期范围内。如果星期几是输入日期，则不会更改日期（GetDate()）
Declare @DayOfWeek As Integer = 2 -- Monday
Select DateAdd(Day, ((DatePart(dw,GetDate()) + (7 - @DayOfWeek)) * -1) % 7, Convert(Date,GetDate()))
回答by Pondlife
The simplest solution to find the previous business day is to use a calendar tablewith a column called IsBusinessDayor something similar. The your query is something like this:
查找前一个工作日的最简单解决方案是使用带有名为或类似列的日历表IsBusinessDay。你的查询是这样的：
select max(BaseDate)
from dbo.Calendar c
where c.IsBusinessDay = 0x1 and c.BaseDate < @InputDate
The problem with using functions is that when (not if) you have to create exceptions for any reason (national holidays etc.) the code quickly becomes unmaintainable; with the table, you just UPDATEa single value. A table also makes it much easier to answer questions like "how many business days are there between dates X and Y", which are quite common in reporting tasks.
使用函数的问题在于，当（不是如果）出于任何原因（国定假日等）必须创建异常时，代码很快就会变得无法维护；有了表，你就只有UPDATE一个值。表格还可以更轻松地回答诸如“日期 X 和 Y 之间有多少个工作日”之类的问题，这在报告任务中很常见。
回答by paul
select
dateadd(dd,
case DATEPART(dw, getdate())
when 1
then -2
when 2
then -3
else -1
end, GETDATE())
回答by Jason Lloyd
More elegant:
更优雅：
select DATEADD(DAY,
CASE when datepart (dw,Getdate()) < 3 then datepart (dw,Getdate()) * -1 + -1 ELSE -1 END,
cast(GETDATE() as date))
回答by stamina_josh
thanks for the tips above, I had a slight variant on the query in that my user needed all values for the previous business date. For example, today is a Monday so he needs everything between last Friday at midnight through to Saturday at Midnight. I did this using a combo of the above, and "between", just if anyone is interested. I'm not a massive techie.
感谢上面的提示，我在查询上有一个细微的变化，因为我的用户需要上一个营业日期的所有值。例如，今天是星期一，因此他需要从上周五午夜到周六午夜之间的所有内容。我使用上述和“之间”的组合来做到这一点，只要有人感兴趣。我不是一个庞大的技术人员。
-- Declare a variable for the start and end dates.
declare @StartDate as datetime
declare @EndDate as datetime
SELECT @StartDate = DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())
WHEN 'Sunday' THEN -2
WHEN 'Monday' THEN -3
ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))
select @EndDate = @StartDate + 1
select @StartDate , @EndDate
-- Later on in the query use "between"
and mydate between @StartDate and @EndDate
