Solved

# Exclude Weekends

Posted on 2004-11-08
4,420 Views
Hi,
I have the Following Query.

Set @SQL = @SQL + ' where (DateDiff("d", ORDR_DATE, ORDR_ShipDate) <= 3)'

What is does is returns the difference b/w two dates, if LESS then or equall to 3 then its ok. What I want to do is, if its saturday or Sunday then DONT Count those days, I want to return different depending upon PURE Weekdays. M - F.

Thanks
0

LVL 6

Expert Comment

I found a rather lively discussion on this topic on:

http://dbforums.com/t802566.html

Duane
0

LVL 6

Expert Comment

I did not write this, I found it on:

I know it is in VB script, but it should be easy to follow the code and convert it to a stored proc.

Duane

Here's a routine I wrote in VB and converted to VBScript that gives
the number of business days between two dates:

'VBScript does not support the VBA function IIF
'so we can mimic it with this function
Function IIf(i,j,k)
If i Then IIf = j Else IIf = k
End Function

Date1 = CDate("4/1/01")
Date2 = CDate("5/16/01")

Dim days
Dim dowdiff
Dim weekd
days = DateDiff("d", Date1, Date2)
dowdiff = IIf(Weekday(Date1) < 7, Weekday(Date2), 1) _
- Weekday(Date1)
weekd = IIf(days = 0, 0, Int(days / 7) * 5 + _
IIf(dowdiff >= 0 And dowdiff < 6, dowdiff, dowdiff + 5))

Response.Write weekd

Jeremy
0

LVL 23

Author Comment

Pure T-SQL Solution would be great.

Thanks
0

LVL 32

Expert Comment

A couple of theory questions (under the useful assumption that Murphy's law is the supreme law of computing):

What should happen if the first date, or the last date, fall on a weekend? (Don't say that it cannot happen, because people can manually edit the tables, unless you have constraints limiting what can be entered in those fields to day of week 2 to 6).

Do you want a general purpose solution (Given two dates, return # of weekdays between them) or a special case solution (given two dates, tell if at least three working days [M-F] fall between them)?
0

LVL 23

Author Comment

ok let me explain..

Lets look at this Query

DateDiff("d", ORDR_DATE, ORDR_ShipDate) <= 3)

ORDR_DATE = Friday
Ship Date   = Tuesday

Now Basicly DateDiff will return the overall Difference b/w these two dates(FRI, SAT, Sun, Mon, Tues), But I dont want this, I want "NOT" to include Sat and Sun in my Query.

in my Application I am Looking for all the orders Shipped within 3 days. 3 Business Days I want to Count. if order was placed on Thursday and was shipped on Monday then its ok for the Query if we take out weekends, but NOT ok if we just run the generic DateDiff Function.

Hope it makes sense.

Thanks
0

LVL 32

Accepted Solution

You can always try the query:

Set @SQL = @SQL + ' where ((Datediff(dd, ORDR_DATE, ORDR_ShipDate) - (Datediff(ww, ORDR_DATE, ORDR_Shipdate) * 2)) <= 3)'
0

LVL 10

Expert Comment

Hi

Declare @StartDate datetime
Declare @EndDate datetime

set @StartDate = '5-NOV-2004'
set @EndDate = '11-NOV-2004'

SELECT  1 where (DateDiff(dd, @StartDate, @EndDate)<= 3) or  (DateDiff(dd, @StartDate, @EndDate)<= 5 and datepart(weekday,@StartDate) not between 2 and 5 )

try this i'll explain it in a minute

cheers

Len
0

LVL 6

Expert Comment

bhess1 gave me an idea

Duane
0

LVL 10

Expert Comment

This bit catch all dosent really matter

(DateDiff(dd, @StartDate, @EndDate)<= 3)

or  (DateDiff(dd, @StartDate, @EndDate)<= 5 and datepart(weekday,@StartDate) not between 2 and 5 )

Because its 3 days we can only cross over 1 weekend

(weekday,@StartDate) will return 1 = sunday to   6 = saturday,

so providing i wasn't ordered on a monday or tueday the the difference can be <= 5

cheers

Len

0

LVL 10

Expert Comment

Declare @StartDate datetime
Declare @EndDate datetime

set @StartDate = '4-NOV-2004'
set @EndDate = '10-NOV-2004'

SELECT  1 where (DateDiff(dd, @StartDate, @EndDate)<= 3) or  (DateDiff(dd, @StartDate, @EndDate)<= 5 and (datepart(weekday,@StartDate) < 2 or datepart(weekday,@StartDate) > 3) )

this is it, i made an error

cheers

Len
0

LVL 23

Author Comment

seems like bhess solution works

Set @SQL = @SQL + ' where ((Datediff(dd, ORDR_DATE, ORDR_ShipDate) - (Datediff(ww, ORDR_DATE, ORDR_Shipdate) * 2)) <= 3)'

I did some testing and seems like its Taking out Saturday and Sunday from the Range.

Thanks
0

LVL 32

Expert Comment

Datediff(dd...) of course, returns the number of days difference between two dates.

Datediff(ww...) returns the number of Weeks difference between two dates.

If two dates are in the same week, the ww compare returns 0.

Given this, you can see that:
Friday week 1 to monday week 2 is a 1 week difference.

Now, the separation for each week is two weekend days, so (2 * Week difference) gives the number of Saturdays and Sundays between date1 and date2.
Subtracting the number of weekend days from the total number of days between date1 and date2 returns the number of weekdays between the two of them.

Friday to next monday returns
(4 days) - (2 weekend days) = 2 days.
0

LVL 23

Author Comment

one More Question, maybe i am going Crazy.

by Default T-SQL Takes Monday as a New Week or Sunday?
0

LVL 10

Expert Comment

Sunday is the first day of the week

cheers

Len
0

## Featured Post

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…