UDF for date ranges around a given date (updated)

,

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...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_now]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_now]
GO

create view v_now
as
/*******************************************************************
Returns the current datestamp
*******************************************************************/
select getdate() now
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufn_date_ranges]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ufn_date_ranges]
GO

CREATE    function ufn_date_ranges (@Date datetime)
returns @Date_Period_Tab table (
	  period varchar(30)
	, period_length varchar(20)
	, prev_same_next varchar(10)
	, start_date smalldatetime
	, end_date datetime)

as

/*******************************************************************
--Name        : ufn_date_ranges
--Server      : Generic 
--Description : Function to return records of date ranges
--              around a specific date
--
--Note        : The function requires a view 'v_now':
--              	create view v_now
--			as
--			select getdate() now
--
--Comments    : The function can accept null as a parameter
--              in which case it will use getdate() from the v_now
--              view.
--
--Parameters  : @Date datetime 
-- 
--Date        : 08/24/2003
--Author      : Paul Shotts
--
--History     : 
*******************************************************************/

begin
	declare	  @MonthStartS smalldatetime
		, @MonthStartD datetime
		, @Now datetime

	select @Now = now from v_now
	select @Date = isnull(@Date, @Now)

	select @MonthStartD = dateadd(month,datediff(month,0,@Date),0)
	select @MonthStartS = cast(@MonthStartD as smalldatetime)

	insert into @Date_Period_Tab
	select	  'Same Day' as period
		, 'Day' as period_length
		, 'Same' as prev_same_next
		, cast(datediff(day,0,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,-1,@Date) as datetime)) as end_date
	union all
	select	  'Previous Day' as period
		, 'Day' as period_length
		, 'Previous' as prev_same_next
		, cast(datediff(day,1,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,0,@Date) as datetime)) as end_date
	union all
	select	  'Next Day' as period
		, 'Day' as period_length
		, 'Next' as prev_same_next
		, cast(datediff(day,-1,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,-2,@Date) as datetime)) as end_date
	union all
	select	  'Same Week' as period
		, 'Week' as period_length
		, 'Same' as prev_same_next
		, cast(datediff(day,datepart(weekday,@Date)-1,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,datepart(weekday,@Date)-8,@Date) as datetime)) as end_date
	union all
	select	  'Previous Week' as period
		, 'Week' as period_length
		, 'Previous' as prev_same_next
		, cast(datediff(day,datepart(weekday,@Date)+6,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,datepart(weekday,@Date)-1,@Date) as datetime)) as end_date
	union all
	select	  'Next Week' as period
		, 'Week' as period_length
		, 'Next' as prev_same_next
		, cast(datediff(day,datepart(weekday,@Date)-8,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,datepart(weekday,@Date)-15,@Date) as datetime)) as end_date
	union all
	select	  'Same Month' as period
		, 'Month' as period_length
		, 'Same' as prev_same_next
		, @MonthStartS as start_date
		, dateadd(millisecond,-2,dateadd(month,1,@MonthStartD)) as end_date
	union all
	select	  'Previous Month' as period
		, 'Month' as period_length
		, 'Previous' as prev_same_next
		, dateadd(month,-1,@MonthStartS) as start_date
		, dateadd(millisecond,-2,@MonthStartD) as end_date
	union all
	select	  'Next Month' as period
		, 'Month' as period_length
		, 'Next' as prev_same_next
		, dateadd(month,1,@MonthStartS) as start_date
		, dateadd(millisecond,-2,dateadd(month,2,@MonthStartD)) as end_date
	union all
	select	  'Same Year' as period
		, 'Year' as period_length
		, 'Same' as prev_same_next
		, cast(datediff(day,datepart(dayofyear,@Date)-1,@Date) as smalldatetime) as start_date
		, dateadd(millisecond,-2,dateadd(year,1,cast(datediff(day,datepart(dayofyear,@Date)-1,@Date) as datetime))) as end_date
	union all
	select	  'Previous Year' as period
		, 'Year' as period_length
		, 'Previous' as prev_same_next
		, dateadd(year,-1,cast(datediff(day,datepart(dayofyear,@Date)-1,@Date) as smalldatetime)) as start_date
		, dateadd(millisecond,-2,cast(datediff(day,datepart(dayofyear,@Date)-1,@Date) as datetime)) as end_date
	union all
	select	  'Next Year' as period
		, 'Year' as period_length
		, 'Next' as prev_same_next
		, dateadd(year,1,cast(datediff(day,datepart(dayofyear,@Date)-1,@Date) as smalldatetime)) as start_date
		, dateadd(millisecond,-2,dateadd(year,2,cast(datediff(day,datepart(dayofyear,@Date)-1,@Date) as datetime))) as end_date
	union all
	select	  'Same Quarter' as period
		, 'Quarter' as period_length
		, 'Same' as prev_same_next
		, case month(@Date)%3
			when 1 then @MonthStartS
			when 2 then dateadd(month,-1,@MonthStartS)
			when 0 then dateadd(month,-2,@MonthStartS)
		  end as start_date
		, dateadd(millisecond,-2,
		  case month(@Date)%3
			when 1 then dateadd(month,3,@MonthStartD)
			when 2 then dateadd(month,2,@MonthStartD)
			when 0 then dateadd(month,1,@MonthStartD)
		  end) as end_date
	union all
	select	  'Previous Quarter' as period
		, 'Quarter' as period_length
		, 'Previous' as prev_same_next
		, case month(@Date)%3
			when 1 then dateadd(month,-3,@MonthStartS)
			when 2 then dateadd(month,-4,@MonthStartS)
			when 0 then dateadd(month,-5,@MonthStartS)
		  end as start_date
		, dateadd(millisecond,-2,
		  case month(@Date)%3
			when 1 then @MonthStartD
			when 2 then dateadd(month,-1,@MonthStartD)
			when 0 then dateadd(month,-2,@MonthStartD)
		  end) as end_date
	union all
	select	  'Next Quarter' as period
		, 'Quarter' as period_length
		, 'Next' as prev_same_next
		, case month(@Date)%3
			when 1 then dateadd(month,3,@MonthStartS)
			when 2 then dateadd(month,2,@MonthStartS)
			when 0 then dateadd(month,1,@MonthStartS)
		  end as start_date
		, dateadd(millisecond,-2,
		  case month(@Date)%3
			when 1 then dateadd(month,6,@MonthStartD)
			when 2 then dateadd(month,5,@MonthStartD)
			when 0 then dateadd(month,4,@MonthStartD)
		  end) as end_date
	union all
	select	  'Same Half Year' as period
		, 'Half Year' as period_length
		, 'Same' as prev_same_next
		, case month(@Date)%6
			when 1 then @MonthStartS
			when 2 then dateadd(month,-1,@MonthStartS)
			when 3 then dateadd(month,-2,@MonthStartS)
			when 4 then dateadd(month,-3,@MonthStartS)
			when 5 then dateadd(month,-4,@MonthStartS)
			when 0 then dateadd(month,-5,@MonthStartS)
		  end as start_date
		, dateadd(millisecond,-2,
		  case month(@Date)%6
			when 1 then dateadd(month,6,@MonthStartD)
			when 2 then dateadd(month,5,@MonthStartD)
			when 3 then dateadd(month,4,@MonthStartD)
			when 4 then dateadd(month,3,@MonthStartD)
			when 5 then dateadd(month,2,@MonthStartD)
			when 0 then dateadd(month,1,@MonthStartD)
		  end) as end_date
	union all
	select	  'Previous Half Year' as period
		, 'Half Year' as period_length
		, 'Previous' as prev_same_next
		, case month(@Date)%6
			when 1 then dateadd(month,-6,@MonthStartS)
			when 2 then dateadd(month,-7,@MonthStartS)
			when 3 then dateadd(month,-8,@MonthStartS)
			when 4 then dateadd(month,-9,@MonthStartS)
			when 5 then dateadd(month,-10,@MonthStartS)
			when 0 then dateadd(month,-11,@MonthStartS)
		  end as start_date
		, dateadd(millisecond,-2,
		  case month(@Date)%6
			when 1 then @MonthStartD
			when 2 then dateadd(month,-1,@MonthStartD)
			when 3 then dateadd(month,-2,@MonthStartD)
			when 4 then dateadd(month,-3,@MonthStartD)
			when 5 then dateadd(month,-4,@MonthStartD)
			when 0 then dateadd(month,-5,@MonthStartD)
		  end) as end_date
	union all
	select	  'Next Half Year' as period
		, 'Half Year' as period_length
		, 'Next' as prev_same_next
		, case month(@Date)%6
			when 1 then dateadd(month,6,@MonthStartS)
			when 2 then dateadd(month,5,@MonthStartS)
			when 3 then dateadd(month,4,@MonthStartS)
			when 4 then dateadd(month,3,@MonthStartS)
			when 5 then dateadd(month,2,@MonthStartS)
			when 0 then dateadd(month,1,@MonthStartS)
		  end as start_date
		, dateadd(millisecond,-2,
		  case month(@Date)%6
			when 1 then dateadd(month,12,@MonthStartD)
			when 2 then dateadd(month,11,@MonthStartD)
			when 3 then dateadd(month,10,@MonthStartD)
			when 4 then dateadd(month,9,@MonthStartD)
			when 5 then dateadd(month,8,@MonthStartD)
			when 0 then dateadd(month,7,@MonthStartD)
		  end) as end_date

	return

end
GO

Rate

Share

Share

Rate