如何使用sql server获取当前工作日的一周内的上一个工作日 [英] How to get Previous business day in a week with that of current Business Day using sql server

问题描述

我有一个在工作日(周一至周五)运行的 ssis 包.如果我在星期二收到文件，背景(DB)，它需要前一个工作日的日期并进行一些交易.如果我在星期五运行该作业，它必须获取星期一的日期并处理交易.

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.

我使用以下查询来获取上一个营业日期

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

如果我执行这个查询，我会得到昨天 Transactios 的结果.如果我在星期一运行相同的查询，它必须获取星期五的交易而不是星期日.

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.

推荐答案

SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                        WHEN 'Sunday' THEN -2 
                        WHEN 'Monday' THEN -3 
                        ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

我更喜欢将 DATENAME 用于这样的事情而不是 DATEPART，因为它不需要设置 DATEFIRST 并确保时间/本地机器上的日期设置不会影响结果.最后 DATEDIFF(DAY, 0, GETDATE()) 将删除 GETDATE() 的时间部分，无需转换为 varchar(慢得多).

I prefer to use DATENAME for things like this over DATEPART as it removes the need for Setting DATEFIRST And 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).

编辑(将近 2 年)

这个答案在我的 SO 职业生涯中很早就出现了，每次它被点赞时都会让我很恼火，因为我不再同意使用 DATENAME 的观点.

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.

更可靠的解决方案是:

SELECT  DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE()));

这适用于所有语言和 DATEFIRST 设置.

This will work for all language and DATEFIRST settings.

这篇关于如何使用sql server获取当前工作日的一周内的上一个工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！