DATEDIFF - Weekdays

1. Member
Join Date
Dec 1969
Posts
94

## DATEDIFF - Weekdays

I&#039m trying to put together a statement in SQL Server or ASP that counts the number of days between two dates EXCLUDING weekends. Is there an eas way of doing this?<BR><BR>Thanks!<BR><BR>Marko

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## RE: DATEDIFF - Weekdays

&nbsp;<BR>Hmmm...not as simple as it looks, at first glance.<BR><BR>Consider: Diff between Monday of week 1 and Friday of week 2 is 11 days, minus Sat/Sun gives 9 weekdays.<BR><BR>But: Diff between Friday week 1 and Tuesday week 3 is also 11 days, but minus *two* Sat/Sun gives only 7 weekdays.<BR><BR>What about holidays? Need to exclude them, as well?? <BR><BR>Looks to me like the best way to do this might be to "back up" both dates to Monday of the week they are in. Then get number of weeks, multiply by 5, and finally adjust for the actual day of the week instead.<BR><BR>Let&#039s try it on my above scenarios:<BR><BR>(1) Diff between Monday of week 1 and Friday of week 2.<BR>Already on Monday in week 1.<BR>Back up 4 days from Friday to Monday in week 2.<BR>Get DateDiff("w"...) and see 1 week. Multiply by 5.<BR>Then advance from Monday to Friday in week 2 (adding 4 days) to get answer: 9. Correct.<BR><BR>(2) Diff between Friday week 1 and Tuesday week 3.<BR>Back up Friday in week 1 to Monday.<BR>Back up Tuesday in week 3 to Monday.<BR>Get DateDiff("w") and see 2 weeks. Multiply by 5. 10 days.<BR>BUT...<BR>Forward to Friday in week 1 means SUBTRACT 4 days (now at 6 days).<BR>Forward to Tuesday in week 3 means ADD 1 day. Result: 7 days, correct.<BR><BR>Yeah, I think that algorithm works fine. Give it a try!<BR><BR><BR> is also 11 days, but minus *two* Sat/Sun gives only 7 weekdays.<BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Here's the code: "Weekdays.asp"

&nbsp;<BR>A quick and dirty hack on the suggestion of previous message. Doesn&#039t account for holidays. I leave that as an exercise for the reader. (Also does not check to see if user entered dates that were on weekends!)<BR><BR>************** FILE: Weekdays.asp ************<BR><BR>&#060;HTML&#062;&#060;BODY&#06 2;<BR><BR>&#060;%<BR>&#039 assume we won&#039t get good dates<BR>date1 = "BAD"<BR>date2 = "BAD"<BR><BR>&#039 get dates from prior time on this page...if any<BR>On Error Resume Next<BR>date1 = CDate( Request.Form("Date1") )<BR>date2 = CDate( Request.Form("Date2") )<BR>On Error Goto 0<BR><BR>&#039 if both dates appear to be okay...<BR>If date1 &#060;&#062; "BAD" AND date2 &#060;&#062; "BAD" Then<BR> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 always go "forward" even if user didn&#039t<BR>&nbsp;&nbsp;&nbsp;&nbsp;If date1 &#062; date2 Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;temp = date1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;date1 = date2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;date2 = temp<BR>&nbsp;&nbsp;&nbsp;&nbsp;End If<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 what day of week is each? Making Monday be zero, though<BR>&nbsp;&nbsp;&nbsp;&nbsp;wkday1 = Weekday(date1,vbMonday)<BR>&nbsp;&nbsp;&nbsp;&nbsp ;wkday2 = Weekday(date2,vbMonday)<BR><BR>&nbsp;&nbsp;&nbsp;& nbsp;&#039 so "back up" each date to the preceding Monday<BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 (might back up zero days...that&#039s okay)<BR>&nbsp;&nbsp;&nbsp;&nbsp;monday1 = DateAdd("d", - wkday1, date1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;monday2 = DateAdd("d", - wkday2, date2)<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 count the weeks<BR>&nbsp;&nbsp;&nbsp;&nbsp;weeks = DateDiff( "w", monday1, monday2, vbMonday )<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 5 weekdays per week<BR>&nbsp;&nbsp;&nbsp;&nbsp;days = weeks * 5<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 then correct for the actual days of the week involved!<BR>&nbsp;&nbsp;&nbsp;&nbsp;days = days - wkday1 + wkday2<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp; &nbsp;&nbsp;&#039 simple as that...<BR>&nbsp;&nbsp;&nbsp;&nbsp;Response.Write "There are " & days & " &#060;b&#062;weekdays&#060;/b&#062; between " _<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ; & date1 & " and " & date2 <BR>End If<BR>%&#062;<BR><BR>&#060;HR&#062;<BR>&#060;FORM Method="POST"&#062;<BR>Enter first date in the form MM/DD/YYYY: &#060;INPUT Name="Date1"&#062;&LT;BR&GT;<BR>Enter second date in the form MM/DD/YYYY: &#060;INPUT Name="Date2"&#062;&LT;BR&GT;<BR>&nbsp;&LT;BR&GT;<B R>&#060;INPUT Type=Submit Value="Push to see days between dates"&#062;&LT;BR&GT;<BR>&#060;/FORM&#062;<BR>&#060;/BODY&#062;&#060;/HTML&#062;<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•