An SQL querythat shows the last business day of the month.

Question

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 21-Mar-18 7:37am

VaDigar

Updated 21-Mar-18 8:39am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 21-Mar-18 13:40pm

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Answer 1 · 2018-03-21T08:40:00

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+.

Maciej Los · Answer 2 · 2018-03-21T07:55:00

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

An SQL querythat shows the last business day of the month.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0