15,035,600 members
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
Maciej Los 21-Mar-18 13:40pm

I see it.
Do i have to do something else?

## Solution 1

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;
--...
--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
Wendelius 22-Mar-18 0:45am

Maciej Los 22-Mar-18 1:34am

Thank you, Mika.

## Solution 2

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
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)
END```

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

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