SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Thursday, July 06, 2006

How write a sp to return consecutive dates except Sat and Sun?

SQL Apprentice Question
As subject. I would like to write a sp that accept 2 inputs, StartDate
and NoOfDays, then returns a recordset of

- totally no. of days matching input
- no sat and sun


e.g. NoOfDays = 10
StartDate = 2006/7/4


then, the result would be


returndate:
2006/7/4
2006/7/5
2006/7/6
2006/7/7
2006/7/10
2006/7/11
2006/7/12
2006/7/13
2006/7/14
2006/7/17



Celko Answers
>> I would like to write a sp that accept 2 inputs, StartDate and NoOfDays, then returns a recordset of > - totally no. of days matching input - no sat and sun <<


Look up calendar tables. They will let you handle holidays, weekends
and many other temporal problems easily.

No comments: