CODEWITHSUNDEEP
sqlsql-servert-sql
Venaikat
Venaikat

Reputation: 197

Number of working days between two dates

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.

Upvotes: 2

Views: 15611

Answers (4)

Yoann Goudot
Yoann Goudot

Reputation: 1

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 )

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280605

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;

Upvotes: 7

ctorx
ctorx

Reputation: 6951

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.

Upvotes: 1

jayu
jayu

Reputation: 337

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)

Upvotes: 3

Related Questions