UDF for date ranges around a given date (updated)

Total article views: 265 | Views in the last 30 days: 15

 Rate this |   Join the discussion |  Briefcase

UDF - SQL 2000 and higher

This function returns an 18 row table of date ranges around a given date.
The ranges are: Day, Week (Sunday to Saturday), Month, Quarter, Half Year, Year.
For each range there are 3 values: Previous, Same and Next

Example of usage:
Joined to an orders table:

Select r.period, count(o.order_id)
from orders o
inner join dbo.ufn_date_ranges('2003-04-01') r
on o.order_date between r.start_date and r.end_date
where r.period_length = 'quarter'
group by r.period

The function requires a view v_now, which is also created by this script.
When the function is passed null as its parameter instead of a specific datetime value it gives results for the current timestamp: ie today, yesterday, tomorrow or this month and last month etc...

Total article views: 265 | Views in the last 30 days: 15