# Calculate nth business day of the current month

Looking for a simple inline statement (MSSQL) that can return the current workday number of the month (number of days elapsed minus Saturday and Sunday) E.g. NOV 11 2019 would be the 7th working day.

• I know you want an inline way, but i'd consider a dimension table for this especially since you said workday and not weekday
– S3S
Nov 11, 2019 at 21:53
• use `a dimension table` as suggested. And if you need a inline way, create a function that calculate the `nth working day` Nov 12, 2019 at 1:42

Your title asked for the "nth business day of the current month". And the body of your question flips that around and asks to find the "n" for the current day. I'm gonna go with the question as asked in the body.

Jeff Moden has a really good write-up here. He wrote it as a function, with parameters and error handling and all of that other good stuff. But you want it inline, so we just need to replace the `@EndDate` pieces with `GETDATE()`, and replace the `@StartDate` pieces with a little bit of date math to get the first day of the current month.

``````SELECT DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), GETDATE()) + 1
- DATEDIFF(week, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), GETDATE()) * 2
- (CASE WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) = 'Sunday'
THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(WEEKDAY, GETDATE()) = 'Saturday'
THEN 1 ELSE 0 END) as WorkdayNumber
``````

Because you wanted this as inline code, for the current day, it's not easy to test it for other days. But you can swap `@EndDate` back in to make it easier to test.

``````DECLARE @EndDate DATETIME = '20191111'
SELECT DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, @EndDate), 0), @EndDate) + 1
- DATEDIFF(week, DATEADD(month, DATEDIFF(month, 0, @EndDate), 0), @EndDate) * 2
- (CASE WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @EndDate), 0)) = 'Sunday'
THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(WEEKDAY, @EndDate) = 'Saturday'
THEN 1 ELSE 0 END) as WorkdayNumber
``````

Putting in some sample values for `@EndDate`, you get these results, which seem to meet your expectations.

``````EndDate      WorkdayNumber
-------      -------------
20191101     1
20191104     2
20191105     3
20191111     7
20191115     11
20191129     21
``````

Notes:

• This doesn't contemplate holidays.
• The DATENAME part depends on your configured language.