[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exclude Weekends

Posted on 2004-11-08
14
Medium Priority
?
4,435 Views
Last Modified: 2011-12-19
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.

Please Advise.

Thanks
0
Comment
Question by:Saqib Khan
  • 4
  • 4
  • 3
  • +1
14 Comments
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12526074
I found a rather lively discussion on this topic on:

http://dbforums.com/t802566.html

Duane
0
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12526094
I did not write this, I found it on:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3b3c7d26.157897584%40news.microsoft.com&rnum=3&prev=/groups%3Fnum%3D50%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DWEEKDAYS%2B%2BVBSCRIPT%2BBETWEEN%26btnG%3DGoogle%2BSearch

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

by:Saqib Khan
ID: 12526311
Pure T-SQL Solution would be great.

Thanks
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 32

Expert Comment

by:Brendt Hess
ID: 12526324
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

by:Saqib Khan
ID: 12526420
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

by:
Brendt Hess earned 2000 total points
ID: 12526714
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

by:lengreen
ID: 12526785
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

by:Duane Lawrence
ID: 12526786
bhess1 gave me an idea

select datediff( ww, getdate(), dateadd(d,20,getdate()))*5

Duane
0
 
LVL 10

Expert Comment

by:lengreen
ID: 12526941
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

by:lengreen
ID: 12526966
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

by:Saqib Khan
ID: 12526972
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.

Bhess can you please give me little bit of explaination about this Script, this way i will know what i am doing

Thanks
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 12527035
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

by:Saqib Khan
ID: 12527267
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

by:lengreen
ID: 12527429
Sunday is the first day of the week

cheers

Len
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question