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)

asked Oct 12, 2009 at 12:19 PM in Default

Andrew Clarke
56 ● 2 ● 2 ● 4

Do you want to allow for bank holidays? And if so, how localized do you want to be?!

Oct 12, 2009 at 12:27 PM Melvyn Harbour 1 ♦♦

Just to make sure you see it. My post below does not take holidays into account, but is localized.

Oct 12, 2009 at 12:36 PM Jack Corbett

I'm not worried about public holidays.

Oct 13, 2009 at 03:54 PM Andrew Clarke

(comments are locked)

5 answers:

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

answered Oct 13, 2009 at 10:37 AM

Peso
1.6k ● 5 ● 6 ● 9

(comments are locked)

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.

answered Oct 12, 2009 at 12:35 PM

Jack Corbett
1.1k ● 3 ● 4 ● 7

(comments are locked)

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

answered Oct 12, 2009 at 07:13 PM

Adam Haines
91 ● 2 ● 2

(comments are locked)

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.

answered Dec 30, 2012 at 12:08 AM

GPO
4.9k ● 42 ● 52 ● 58

(comments are locked)

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

answered Aug 13, 2012 at 05:34 AM