x

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)

more ▼

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

Andrew Clarke gravatar image

Andrew Clarke
46 2 2 2

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)
10|1200 characters needed characters left

5 answers: sort oldest

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
more ▼

answered Oct 13, 2009 at 10:37 AM

Peso gravatar image

Peso
1.6k 5 6 8

This seems to give the right answer most of the time, but when I count up the number of working days in February 2008, your code thinks it is 23 working days and my windows calendar says it is 21 days. Am I doing something wrong.
Oct 13, 2009 at 04:04 PM Andrew Clarke
Change "UNION ALL" to "UNION".
Oct 13, 2009 at 08:02 PM Peso
Did the change from "UNION ALL" to "UNION" solve your problem?
Nov 06, 2009 at 08:17 AM Peso
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12, 2009 at 12:35 PM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left

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

answered Oct 12, 2009 at 07:13 PM

Adam Haines gravatar image

Adam Haines
91 1

(comments are locked)
10|1200 characters needed characters left

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

answered Aug 13, 2012 at 05:34 AM

Amar_jain gravatar image

Amar_jain
0 1

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Dec 30, 2012 at 12:08 AM

GPO gravatar image

GPO
2.9k 35 38 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x346
x10

asked: Oct 12, 2009 at 12:19 PM

Seen: 9994 times

Last Updated: Jan 02, 2013 at 02:55 PM