一个SQL查询，显示该月的最后一个工作日。 [英] An SQL querythat shows the last business day of the month.

问题描述

我们谈论的是正常周（M-F），不包括假期。请参阅下面的查询。谢谢！



我的尝试：

We're talking about normal weeks (M-F), and not including holidays.Please see my query below. Thank you!

What I have tried:

CREATE FUNCTION dbo.ufn_LastBusinessDayOfMonth (@Dt datetime) RETURNS datetime
AS
BEGIN
DECLARE @dt2 datetime
DECLARE @Df int
DECLARE @dSat int
DECLARE @dSun int

SELECT @dt2 = DATEADD(D, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @Dt), 0))

SELECT @dSat = DATEPART(dw, '2018-01-06') -- Known Saturday SELECT @dSun = (@dSat % 7) + 1

SELECT @dt2 = ( CASE WHEN DATEPART(dw, @dt2) = @dSun THEN DATEADD(DAY, -2, @dt2) WHEN DATEPART(dw, @dt2) = @dSat THEN DATEADD(DAY, -1, @dt2) ELSE @dt2 END)

RETURN @dt2

END

推荐答案

对于MS SQL Server 2012及更高版本，您可以使用： EOMONTH（Transact-SQL） [ ^ ]

接下来，根据工作日的情况，您可以计算最后一个工作日。



检查：

For MS SQL Server 2012 and higher you can use: EOMONTH (Transact-SQL)[^]
Next, depending on what weekday it is, you can calculate last working day.

Check this:

SET DATEFIRST 1;
--1 -> Monady
--...
--7 -> Sunday

DECLARE @dt DATE = '2018-09-01'
DECLARE @dt2 DATE = EOMONTH(@dt)
DECLARE @NoOfDays INT = DATEPART(DW, @dt2)

SET @NoOfDays = CASE WHEN @NoOfDays >5 THEN 5-@NoOfDays ELSE  0 END

SELECT @dt2 AS EndOfMonth, @NoOfDays  AS [NoOfDays], DATEADD(DD, @NoOfDays, @dt2) AS LastWorkingDayOfMonth

我只能在工作中访问SQL 2008R2，因此您可以依赖于在所有版本的sql server中工作。



试试这个（告诫 - 没有经过广泛测试，但是对于测试日期，它将在8/30/2018周五返回，并且下个月它将于08/30/2018周一返回）：

I only have access to SQL 2008R2 at work, so you can count on this working in all versions of sql server.

Try this (caveat - not extensively tested, but for the testdate indicated, it returns Friday 8/30/2018, and for the next month it returns Monday 04/30/2018):

SET DATEFIRST 1;
declare @testDate date = '03/21/2018';
declare @lastWorkDay date = CASE WHEN DATEPART(WEEKDAY, DATEADD(DAY,-1,DATEADD(month, DATEDIFF(month, 0, @testdate) + 1, 0))) <= 5 
                                 THEN DATEADD(DAY,-1,DATEADD(month, DATEDIFF(month, 0, @testdate) + 1, 0))
                                 ELSE DATEADD(DAY,-(7-DATEPART(WEEKDAY, DATEADD(DAY,-1,DATEADD(month, DATEDIFF(month, 0, @testdate) + 1, 0)))+1),DATEADD(month, DATEDIFF(month, 0, @testdate) + 1, 0))
                                 END;
select @lastWorkDay;

当然，在我回到家之前我无法测试这个，但是有sql server 2012及更高版本的代码。如果您运行的是2012或更高版本，只需替换以下代码上面的代码：

Of course, I can't test this until I get home, but there's the code for sql server 2012 and higher . If you are running 2012 or higher, simply replace the code above following code:

declare @lastWorkDay date = CASE WHEN DATEPART(WEEKDAY, EOMONTH(@testDate)) <= 5 
                                  THEN EOMONTH(@testDate)
                                  ELSE DATEADD(DAY, -(7-DATEPART(WEEKDAY, EOMONTH(@testDate))),EOMONTH(@testDate))
                                  END

编辑============================



注意：上面的所有代码已更正并验证在sql server 2012 +中运行。

EDIT ============================

NOTE: All of the code above has been corrected and verified to run in sql server 2012+.

这篇关于一个SQL查询，显示该月的最后一个工作日。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！