question

Andrew Clarke avatar image
Andrew Clarke asked

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)

t-sqlsql-servercalendar
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you want to allow for bank holidays? And if so, how localized do you want to be?!
0 Likes 0 ·
Just to make sure you see it. My post below does not take holidays into account, but is localized.
0 Likes 0 ·
I'm not worried about public holidays.
0 Likes 0 ·
Peso avatar image
Peso answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jack Corbett avatar image
Jack Corbett answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Adam Haines avatar image
Adam Haines answered

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))
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Amar_jain avatar image
Amar_jain answered
**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.......***
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.