SQL Date Ranges

I run across a lot of scenarios in my work where I have to return a result set for a given time period. A simple example is that a website might need closing prices for the last day, week, month, year, etc.


select *
from PricingTable
where thedate is between
case when @DateRange = 'Weekly' then DATEADD(wk,-1,getdate()) --@WeekDate
when @DateRange = 'Monthly' then DATEADD(mm,-1,getdate()) --@MonthDate
when @DateRange = 'Yearly' then DATEADD(yy,-1,getdate()) --@YearDate
end and @CurrentDate --getdate()


Problems arise however because the date logic does not take into account weekends and holidays when the underlying data might not exist. To solve this problem, we have a UDF which essentially does the following, but in lieu of one, here's what you can do.

1. Set @CurrentDate = current date (getdate())
   
2. Set @WeekDate, @MonthDate and @YearDate = to dateadd -1 time period
   
3. Here's where it gets cool. You want to set each time period = to the highest date from the underlying table as long as it is less than or equal to the raw date you selected before.

   select @WeekDate = MAX(thedate)
from PricingTable
where thedate <= @WeekDate

   That way if the date is a valid day of the week, the date stays the same. Otherwise you get the closest date to it that is valid in the table. Depending on the requirements of your site, you can do something similar to say the minimum date where it is greater than or equal to the raw date, to shift the date the other direction
   
4. repeat this for the dates you have, and you then have working date ranges that are guaranteed to work for the underlying data.
   

In some scenarios, this much level of precision doesn't matter, but I've seen a wide range of problems arise from not using a technique like this that can manifest in strange ways, and be difficult to troubleshoot depending on the complexity of the procedure. If the underlying table is indexed on [thedate], it will speed up these seeks as well, and the extra code can save you some big headaches down the line.

EDIT 9/8/2011:
An alternative to this method is to simply say if the day it lands on is Saturday, subtract 1 day or if Sunday, subtract 2 days if you don't care about holidays. There are other function which can be written to account for US holidays, but this method works in the specific scenario where you a) need to account for holidays and b) don't have a means (yet) of accounting for holidays (or if you deal with international data where holidays could vary greatly).