Click here to Skip to main content
15,035,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
We're talking about normal weeks (M-F), and not including holidays.Please see my query below. Thank you!

What I have tried:

SQL
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
Posted
Updated 21-Mar-18 8:39am
v2
Comments
Maciej Los 21-Mar-18 13:40pm
   
I see it.
Do i have to do something else?

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:
SQL
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
   
v2
Comments
Wendelius 22-Mar-18 0:45am
   
Good advice, a 5
Maciej Los 22-Mar-18 1:34am
   
Thank you, Mika.
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):

SQL
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;


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:

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


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

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900