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.
Author 
Topic 
michpaust
Starting Member
21 Posts 
Posted  20050512 : 14:00:02

Does anyone know what function I would use to find the difference between two dates but will only include Monday to Friday in its calculation? I have already used the datediff function to get the result as an integer but now I need to take Sat and Sunday out from my result. Thanks!
I don't suffer from insanity. I enjoy every minute of it. 

DonAtWork
Master Smack Fu Yak Hacker
2167 Posts 
Posted  20050512 : 14:22:00

Try combining DATEDIFF with DATEPART 


michpaust
Starting Member
21 Posts 
Posted  20050512 : 14:29:22

How exactly would that work? I know datepart would return a number that correspods to the day of the week. Here's an example of what I am looking for datediff(day,xx/xx/2004,yy/yy/2003)= n days. I need to subtract the saturdays and sundays from that result n . How would incorporating datepart in there help. I'm sorrt but I just can't form the syntax in my head.
I don't suffer from insanity. I enjoy every minute of it. 


X002548
Not Just a Number
15586 Posts 

Seventhnight
Master Smack Fu Yak Hacker
2878 Posts 
Posted  20050512 : 14:42:43

if there are no holidays to consider:
the blue part is the real calc. the rest is showing what the steps are.
Declare @aDate datetime, @bDate datetime Set @aDate = '4/22/2005' Set @bDate = '5/12/2005'
Select dayDiff = datediff(dy,@aDate,@bDate), fullweeks = datediff(dy,@aDate,@bDate)/7, weekdays = datediff(dy,@aDate,@bDate)/7*2, startday = datepart(dw,@aDate), extraDays = datediff(dy,@aDate,@bDate)%7, estraWeekDays = case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2 else 0 end, weekdayDiff = datediff(dy,@aDate,@bDate)  (datediff(dy,@aDate,@bDate)/7*2 + case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1 when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7=6 then 2 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2 else 0 end)
Corey
Secret Service Agent: Mr. President, you're urinating on me. President Lyndon Johnson: I know I am. It's my prerogative. 


X002548
Not Just a Number
15586 Posts 
Posted  20050512 : 14:44:24

What about holidaze?
Brett
8) 


jsmith8858
Dr. Cross Join
7423 Posts 
Posted  20050512 : 14:48:47

if the start date and end date are both weekdays, then the total # of week days in between is simply:
(total difference in days)  (total difference in weeks) * 2
or
DateDiff(dd, @start, @end)  DateDiff(ww, @start, @end)*2
Since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends.
If you have a table of holdays, then you simply substract them out as well:
DateDiff(dd, @start, @end)  DateDiff(ww, @start, @end)*2  (select count(*) from holidays where holiday_date between @start and @end)
 Jeff 


michpaust
Starting Member
21 Posts 
Posted  20050512 : 14:57:56

Yes I forgot to take holidays into account also. Thanks to all for all the help.
I don't suffer from insanity. I enjoy every minute of it. 


Seventhnight
Master Smack Fu Yak Hacker
2878 Posts 
Posted  20050512 : 14:58:21

well brett... i did say if holidays are not an issue.
but i like jeffs solution. i didn't realize that the datediff(ww,...) counted crossed boundaries.
Corey
Secret Service Agent: Mr. President, you're urinating on me. President Lyndon Johnson: I know I am. It's my prerogative. 


X002548
Not Just a Number
15586 Posts 
Posted  20050512 : 15:09:51

Neat....I can lose the weekend population...
And Jeff, I like the drunk photo...but now that you've officialy changed it....does that mean your status is chamging?
Brett
8) 


jsmith8858
Dr. Cross Join
7423 Posts 
Posted  20050512 : 15:12:32

not yet .....
 Jeff 


nitinblr
Starting Member
3 Posts 
Posted  20050608 : 04:23:50

thanks very much jeff!!! that was a real help!! 


swatch89
Starting Member
2 Posts 
Posted  20051118 : 14:27:40

This code doesn't work if the difference between the start date and end date is greater than or equal to 7, e.g. @aDate = '11/1/2005', @bDate = '11/18/2005'. After trying to analyze your code and try to figure out what was wrong with it, I decided to create my own function instead:
CREATE function dbo.DateDiffWeekdays (@fromdate datetime, @todate datetime) RETURNS int AS begin declare @procdate datetime, @enddate datetime declare @weekdays int set @procdate = @fromdate set @weekdays = 0
while (@procdate < @todate) begin if (datepart(dw, @procdate + 1) <> 1) and (datepart(dw, @procdate + 1) <> 7) set @weekdays = @weekdays + 1 set @procdate = dateadd(d, 1, @procdate)
end  if @todate is null set @weekdays = null
return @weekdays end
quote: Originally posted by Seventhnight
if there are no holidays to consider:
the blue part is the real calc. the rest is showing what the steps are.
Declare @aDate datetime, @bDate datetime Set @aDate = '4/22/2005' Set @bDate = '5/12/2005'
Select dayDiff = datediff(dy,@aDate,@bDate), fullweeks = datediff(dy,@aDate,@bDate)/7, weekdays = datediff(dy,@aDate,@bDate)/7*2, startday = datepart(dw,@aDate), extraDays = datediff(dy,@aDate,@bDate)%7, estraWeekDays = case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2 else 0 end, weekdayDiff = datediff(dy,@aDate,@bDate)  (datediff(dy,@aDate,@bDate)/7*2 + case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1 when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7=6 then 2 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1 when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2 else 0 end)
Corey
Secret Service Agent: Mr. President, you're urinating on me. President Lyndon Johnson: I know I am. It's my prerogative.



jsmith8858
Dr. Cross Join
7423 Posts 
Posted  20051118 : 16:02:53

.... or you could use the 1line solution I gave if you want to keep things simple ... 


swatch89
Starting Member
2 Posts 
Posted  20051118 : 16:46:14

Your solution doesn't work if the start date falls on a weekend and the end date falls on a weekday. For example:
declare @start smalldatetime, @end smalldatetime set @start = '11/5/05' set @end = '11/9/05' print DateDiff(dd, @start, @end)  DateDiff(ww, @start, @end)*2
The correct number of days should be 3, not 2. My function handles this case correctly :Þ
quote: Originally posted by jsmith8858
.... or you could use the 1line solution I gave if you want to keep things simple ...



igorblackbelt
Constraint Violating Yak Guru
407 Posts 
Posted  20051121 : 12:55:26

I've been using a function we found on the web a while ago an customized to our needs, it might look a little silly but it works for us, it counts a number of days between DateField1 and DateField2, which we use for measurement reporting.
CREATE FUNCTION dbo.[GetWorkingDays] ( @StartDate datetime, @EndDate datetime ) RETURNS INT AS BEGIN DECLARE @WorkDays int, @FirstPart int DECLARE @FirstNum int, @TotalDays int DECLARE @LastNum int, @LastPart int DECLARE @HolidayDifference int
IF (DATEDIFF(day, @StartDate, @EndDate) <1) BEGIN RETURN ( 1 ) END SELECT @TotalDays = DATEDIFF(day, @StartDate, @EndDate), @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 0 WHEN 'Monday' THEN 6 WHEN 'Tuesday' THEN 5 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 3 WHEN 'Friday' THEN 2 WHEN 'Saturday' THEN 0 END, @FirstNum = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 0 WHEN 'Monday' THEN 5 WHEN 'Tuesday' THEN 4 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 2 WHEN 'Friday' THEN 1 WHEN 'Saturday' THEN 0 END IF (@TotalDays < @FirstPart) BEGIN SELECT @WorkDays = @TotalDays+1 END ELSE BEGIN SELECT @WorkDays = (@TotalDays  @FirstPart) / 7 SELECT @LastPart = (@TotalDays  @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart ELSE 0 END SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum END
SELECT @HolidayDifference = (SELECT COUNT(nas.dbo.tblholidays.holidayname) AS CountOfHolidays FROM nas.dbo.tblholidays where nas.dbo.tblholidays.holidaydate >= @StartDate AND nas.dbo.tblholidays.holidaydate <= @EndDate)
SELECT @WorkDays = @WorkDays  @HolidayDifference
 End Modification
RETURN ( @WorkDays ) END

Thanks! Igor. 


Seventhnight
Master Smack Fu Yak Hacker
2878 Posts 
Posted  20051123 : 08:14:47

quote: Originally posted by swatch89
Your solution doesn't work if the start date falls on a weekend and the end date falls on a weekday. For example:
declare @start smalldatetime, @end smalldatetime set @start = '11/5/05' set @end = '11/9/05' print DateDiff(dd, @start, @end)  DateDiff(ww, @start, @end)*2
The correct number of days should be 3, not 2. My function handles this case correctly :Þ
quote: Originally posted by jsmith8858
.... or you could use the 1line solution I gave if you want to keep things simple ...
In his post... he did specify that the begin and end were weekdays, so you would have to make allowances for the other cases... maybe shift the days to a weekday?
Corey
Coworker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." 


sqlmember
Starting Member
7 Posts 
Posted  20051123 : 08:41:28

To make the Jeff's line of code accurate I have made the following change
select DateDiff(dd, @start1, @end)  DateDiff(ww, @start, @end)*2
Khurram Iqbal 


jsmith8858
Dr. Cross Join
7423 Posts 
Posted  20051123 : 09:17:10

quote: Originally posted by sqlmember
To make the Jeff's line of code accurate I have made the following change
select DateDiff(dd, @start1, @end)  DateDiff(ww, @start, @end)*2
Khurram Iqbal
To make it accurate for what situation? this returns 1 if the start and end date are the same day. Surely there are not any weekdays between 11/23/2005 and 11/23/2005 ?
If the start day or the end day might be on a weekend, then you need to define what to do in those situations in your requirements. To me, it doesn't make sense to allow for this, but if you want to, simply define what you wish to do in that situation and handle it.
For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st  how many weekdays are between those dates? 0 or 1 or "undefined" (null) ? You must define this. It can be interpreted in any of those ways.



Andy123
Starting Member
1 Post 
Posted  20090914 : 09:54:34

Hi,
I read through this post looking for code I could use as a column in an SQL query and ended up making my own formula which I thought may be useful to share:
Where DateA is before DateB:
(DateBDateA)  FLOOR((DateBDateA)*2/7)  IF(DOW(DateA) <= DOW(DateB),0,2)
Explanation: (DateBDateA) > no of calendar days
 FLOOR((DateBDateA)*2/7) >minus 2 days for every whole week
 IF(DOW(DateA) <= DOW(DateB),0,2) > minus another 2 days only if the day of the week of A is not before the day of the week of B. I.E. if its mon to thurs this will return '0' if its thursday to mon it will return '2'
This does not account for bank holidays, but it does the basic job.
Andy 






