Solved

How to exclude weekends when using Diffdate function

Posted on 2011-09-17
3
311 Views
Last Modified: 2012-06-27
I have a query to measure delivery performance. Using
DATEDIFF ( DAY , SIH.[Order Date], SIL.[Shipment Date] ) AS [Ship Days],
this gives me a value including weekends.
How do I exclude the weekends?
Thanks.
0
Comment
Question by:kcoxon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36553879
try this
where datepart (dw,  SIL.[Shipment Date]) is not in ('saturday', 'Sunday')

Open in new window

0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 250 total points
ID: 36553893
that was fast reading.. ;)

here is the wanted solutions just replace @startdate, @enddate


select datediff(dd,@startdate,@enddate)-2*datediff(ww,@startdate,@enddate)
+ case when datepart(dw,@startdate)=1 or datepart(dw,@enddate)=7 then -1
when datepart(dw,@startdate)=7 or datepart(dw,@enddate)=1 then +1
else 0 end
0
 

Author Closing Comment

by:kcoxon
ID: 36554133
Just what I wanted. Thanks.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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