Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Date query that excludes weekends and holidays
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/24/2001 :  13:02:27  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Gretchen writes "I am fairly new to SQL and I have a query that I am doing that is based off of dates, the management team would like to exclude weekends in holidays when the query is doing it's calculation. Is there a way to do this within SQL or do I have to create something outside of SQL to perform this. Any help you can give me would be greately appreciated.

Thanks

Gretchen Schoser"

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/24/2001 :  13:34:25  Show Profile  Visit nr's Homepage  Reply with Quote
weekends

where datepart(dw,dte) not in (1,2)
(find out what your system is set up for daynumbers of weekend).

Bank holidays - you will need a table to contain these and have to do a not exists on it in the query.


==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 08/24/2001 :  14:16:04  Show Profile  Reply with Quote
One thing to look out for with this, is that if you are JUST looking to determine the next/previous working day...and say Today is the Friday before a public holiday Monday, so the answer you are looking for is Tuesday......Then the issue can be determined relatively simply with a simple SQL query using both bits that nr suggests.



BUT if what you require is to determine a range of dates from some start date upto and including the next working date (ie covering all non-working dates)...then the issue gets a bit more complicated. Especially if the requirement is to find date(s) backwards or forwards in time from the start date. The problem is further complicated if the requirement is to get the next working dates after the 1st set...a situation where answer #2 depends on answer 1.


Effectively the latter problem I'm describing is how to determine T, T+1, T+2, T-1, T-2, etc...where T=Today (or rather all dates since the last working day, upto but not including the next working day)...and hence T+1 is Today + 1...or the range of dates starting 1 day after the upper range of T.




I'll post a working solution I have to this problem, which can solve all 3 versions of the above, in the next few days if and when I get time.

It's relatively straight forward, but I need to clean it up to take out our extra inhouse requirements.


I posted the above problem some months ago here, but due to lack of responses, I had to sacrifice some valuable braincells that beer hadn't located previously.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000