SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Exclude Weekends in SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mimuk
Starting Member

19 Posts

Posted - 08/26/2014 :  05:56:45  Show Profile  Reply with Quote
I have a sql query which checks for records in 2 days time.
However if I run this on a Thursday or Friday, it will display weekend date, which I want to exclude.

Can someone help so that it excludes Sat/Sun from query so if run on a friday it displays Tuesdays results?

Many thanks in advance.

Query is:

SELECT DISTINCT Orde_OrderQuoteId, orde_reference, orde_description, orde_status, orde_grossamt, orde_grossamt_CID FROM vlistorders WHERE (DATEPART(DY, orde_expiredelivery) = DATEPART(DY, DATEADD(DD, 2, GETDATE())) AND DATEPART(YY, orde_expiredelivery) = DATEPART(YY, DATEADD(DD, 1, GETDATE()))) AND Orde_OrderQuoteId IS NOT NULL ORDER BY Orde_OrderQuoteId

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 08/26/2014 :  10:18:19  Show Profile  Reply with Quote
Change your WHERE clause for the date arithmetic to this:
WHERE 
	orde_expiredelivery = DATEADD(dd,CASE WHEN DATEDIFF(dd,'19000101',GETDATE())%7 IN (3,4) THEN 4 ELSE 2 END,CAST(GETDATE() AS DATE))
Go to Top of Page

mimuk
Starting Member

19 Posts

Posted - 08/26/2014 :  10:46:30  Show Profile  Reply with Quote
Perfect thanks James, works a treat
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000