Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Function for DateDiff

Author  Topic 

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-06-26 : 11:57:02
HI All,

I think i can get help from here. I need a function which can calculcate days between two dates without calculating weekends

Please suggest

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-26 : 12:35:32
Use a calendar table like in the example below:
CREATE TABLE #calendar(Dt DATETIME NOT NULL PRIMARY KEY CLUSTERED, isWeekDay  BIT);
;WITH cte AS
(
SELECT CAST('20130101' AS DATETIME) AS Dt
UNION ALL
SELECT DATEADD(dd,1,dt) FROM cte
WHERE Dt < '20131231'
)
INSERT INTO #calendar
SELECT
dt,
CASE WHEN DATEDIFF(dd,0,dt)%7 >=5 THEN 0 ELSE 1 END
FROM
cte
OPTION (MAXRECURSION 0);

DECLARE @startDate DATETIME = '20130501';
DECLARE @endDate DATETIME = '20130620';

SELECT SUM(CASE WHEN isWeekday = 1 THEN 1 ELSE 0 END)
FROM #calendar
WHERE Dt >= @startDate AND Dt < @endDate

DROP TABLE #calendar;
If you can guarantee that the startdate and enddate are weekdays, then you can use a formula like this:
DECLARE @startDate DATETIME = '20130501';
DECLARE @endDate DATETIME = '20130620';
SELECT DATEDIFF(dd,@startDate,@endDate)-2*DATEDIFF(wk,@startDate,@endDate);
But in general, a calendar table is the easiest.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 22:19:01
James K's query is flexible in that you can add holidays to the calendar table.
If you want to just compute weekdays then you can use this:
[CODE]
DECLARE @StartDate DATE = '20120704';
DECLARE @EndDate DATE = '20130630';

SELECT DATEDIFF(dd, @StartDate, @EndDate)+1 AS NumberOfDays,
(CASE WHEN (DATEDIFF(dd,0,@StartDate) % 7 = 6) OR (DATEDIFF(dd,0,@EndDate) % 7 = 5) THEN
(DATEDIFF(dd, @StartDate, @EndDate)+1 - DATEDIFF(wk, @StartDate, @EndDate)*2) -1
ELSE (DATEDIFF(dd, @StartDate, @EndDate)+1 - DATEDIFF(wk, @StartDate, @EndDate)*2) END) As NumberOfWorkingDays

[/CODE]
Go to Top of Page
   

- Advertisement -