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
