Solved

How to exclude weekends when using Diffdate function

Posted on 2011-09-17
3
305 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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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