Obtaining the Number of working days in a month
What is the quickest/ most effective way in SQL Server of getting the number of working days in a Month (defining the number of working days as being the days monday-friday)
Do you want to allow for bank holidays? And if so, how localized do you want to be?!
Just to make sure you see it. My post below does not take holidays into account, but is localized.
Best Answer
All months always has exactly 20 workdays for the first 28 days. You only have to check day 29, 30 and 31 for being workdays. This code is regional and language independant.
DECLARE @theDate DATETIME
SET @theDate = GETDATE()
SELECT 20 + COUNT(*)
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 28) AS theDate UNION
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 29) UNION
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 30)
) AS d
WHERE DATEPART(DAY, theDate) > 28
AND DATEDIFF(DAY, 0, theDate) % 7 < 5
Here's a way:
SELECT TOP 365
IDENTITY(INT, 1, 1) AS n
INTO
#nums
FROM
sys.all_objects
SELECT
-- this gets the first day of the month so we have something to group on
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n, '1/1/2008')), 0) AS month_Start,
COUNT(*)
FROM
#nums
WHERE
-- this gets only weekdays.
DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) BETWEEN
CASE @@DateFirst
WHEN 7 THEN 2
WHEN 6 THEN 3
WHEN 5 THEN 4
WHEN 4 THEN 5
WHEN 3 THEN 6
WHEN 2 THEN 7
ELSE 1
END AND CASE @@DateFirst
WHEN 7 THEN 6
WHEN 6 THEN 7
WHEN 5 THEN 1
WHEN 4 THEN 2
WHEN 3 THEN 3
WHEN 2 THEN 4
ELSE 5
END
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n, '1/1/2008')), 0)
ORDER BY
month_start
DROP TABLE #nums
You can read this post on my blog to see why I am using @@DateFirst. Obviously this does not take holidays into account. You'd need a calendar table for that.
Another way is to use a numbers table and a little date math.
DECLARE @dt DATETIME
SET @dt = '2009-09-22'
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0) AS Mnth,
SUM(CASE WHEN DATENAME(WEEKDAY,DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)))
IN('Monday','Tuesday','Wednesday','Thursday','Friday') THEN 1
ELSE 0
END) AS Cnt
FROM [master].dbo.[spt_values]
WHERE
type = 'P'
AND DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))
< DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))
I know this is an old thread but what I thought was an obvious solution did not rate a mention. You'll be coming back to this kind of thing time and time again, so why not do all the calculations in advance and store the results in a permanent calendar table (a table of dates - one date per row). Need local public holidays? Add a column that flags them (and even a column that names them if you like). Need phases of the moon? Again, throw in another column. A flag for weekends? First and last day of the month? Week starting date? Financial quarter? Anything that relates to dates will benefit from this approach and make your code much easier to read.
**SELECT to_char(Mdate, 'dy'), To_char(Mdate, 'DD-Mon-YYYY') FROM (select To_date (Level || '/' || '&month' || '/' || &year,'DD-Mon-YYYY') Mdate from dual connect by level <= last_day(to_date('01-' || '&month' || '-' || &year,'DD-Mon-YYYY')) - to_date('01-' || '&month' || '-' || &year, 'DD-Mon-YYYY') + 1) WHERE to_char(Mdate, 'dy') <> 'sun' AND to_char(Mdate, 'dy') <> 'sat';** *In this Query The Month input should be in mon Format like : jan,Feb,Mar,Apr And Year like YYYY : 2012,2011* *it may ask for input 3 times depends on SQL operator* ***ALL D BEST.......***
