2

I want number of working days in between to dates. For example if we have 01-01-2012 and 20-01-2012, i want to get the number of working days in between that two dates using T-SQL.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Venaikat
  • 197
  • 2
  • 5
  • 20
  • What days do you work? What holidays do you have? – Paddy Jun 18 '12 at 11:27
  • 3
    **Not related:** Next time, please tag it with `sql-server` not `sql` and `server` – Mahmoud Gamal Jun 18 '12 at 11:29
  • first must know how many holidays are there then totaldays - holidays = working days..please put your table structure – Nighil Jun 18 '12 at 11:30
  • I suggest you take a look at http://www.codersrevolution.com/index.cfm/2008/10/15/SQL-Server-How-Many-WorkWeek-Days-In-Date-Range or http://weblogs.sqlteam.com/jeffs/archive/2008/07/31/week-days-between-two-dates.aspx which shows a number of different approaches to the problem. – Kerbocat Jun 18 '12 at 11:31
  • Related/ possible duplicate of http://stackoverflow.com/questions/252519/ – James Jenkins Aug 24 '15 at 13:23

4 Answers4

7

Since SQL Server has no idea what your company considers working days, the best answer to this problem is likely going to be to use a calendar table. Once you have a table with past and future dates, with a column like IsWorkDay correctly updated, the query is simple:

SELECT [Date] FROM dbo.Calendar 
  WHERE [Date] >= @start 
  AND [Date] <= @end
  AND IsWorkDay = 1;
Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
3
DECLARE @fromDate datetime, @toDate datetime 
SELECT @fromDate = ' 01-01-2012', @toDate = '20-01-2012'

SELECT (DATEDIFF(day, @fromDate, @toDate) + 1) 
        - (DATEDIFF(week, @fromDate, @toDate) * 2)  
        - (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END) 
        - (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
jayu
  • 327
  • 4
  • 10
  • This handles standard work weeks (Monday to Friday), but doesn't handle non-working days (holidays, school PA days, etc). It also assumes the language setting is `US_English` or similar. And have you tested it with different `SET DATEFIRST` settings? – Aaron Bertrand Jun 18 '12 at 22:18
1

I liked Aaron Bertrand's suggestion so I wrote this code that can be added to your queries. It creates a table variable between 2 dates that you can then use in your query by joining on the CalendarDate column (just remember to strip out any time information before joining). This is based on the typical American work week of Monday through Friday.

DECLARE @StartDate DATE
DECLARE @EndDate DATE

SET @StartDate = '2013-08-19'
SET @EndDate = '2013-08-26'

DECLARE @BusinessDay TABLE
(
    CalendarDate DATETIME,
    IsBusinessDay INT
)

DECLARE @Counter DATETIME = @StartDate
WHILE(@Counter <= @EndDate)
BEGIN
    INSERT INTO @WorkDays
    SELECT @Counter, CASE WHEN DATENAME(WEEKDAY, @Counter) NOT IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END
    SET @Counter = DATEADD(DAY, 1, @Counter)
END

SELECT * FROM @BusinessDay

The downside is this has to be recreated for each query that needs it, so if you're doing this often, a fixed table might be a better way to go.

It can be used like this....

SELECT 
     BusinessDays = SUM(IsBusinessDay)
FROM
     @BusinessDay
WHERE
     CalendarDate BETWEEN @StartDate AND @EndDate

That will give you the count of business days between the two dates. Like many others have said, this obviously does not take into account any holidays or my birthday.

ctorx
  • 6,841
  • 8
  • 39
  • 53
0

Based on previous code, I adapted it to exclude the last day (not asked but I needed it). 

    select (DATEDIFF(dd,@fromDate, @toDate))    
- (DATEDIFF(ww,@fromDate, DATEADD(dd,-1,@toDate)) * 2) 
- (CASE WHEN DATENAME(dw, @fromDate) = 'Sunday' THEN 1 else 0 end)
- (CASE WHEN DATENAME(dw, @toDate) = 'Sunday' THEN 1 else 0 end)

I removed the holydays by using a table containing those dates

   - ( select count(distinct dcsdte) 
    from calendar_table 
    where dcsdte between @fromDate 
        and @toDate )
Yoann Goudot
  • 1
  • 2