Tag Archives: SQL

SQL Date Range Function

Here is a function I came up with to return a range of dates. The function returns a table with FromDate and ToDate fields, while accepting a number of parameters.

@date
–date to be evaluated, usually getdate()
@period
–W=week, M=month, Q=quarter, Y=year => default: week
@ptd
–period to date, otherwise last full period => default: last full
@week_end_day
–specifies the end day of the week to report on
–MO=monday, TU=tuesday, WE=wednesday, TH=thursday, FR=friday, SA=saturday, SU=sunday

This can be useful when cross joining to a table in order to retrieve a dataset with a specific date range.

In this example we want to get all the process runs that were created last week (@ptd=0, last full period), with the week (@period=’W’) ending on Sunday (@week_end_day=’SU’). Since today is Saturday, the FromDate/ToDate will be the previous Monday April 2nd to Last Sunday April 8th.

SELECT *
FROM dbo.Run
CROSS JOIN dbo.fnGetDateRange(getdate(),’W’,0,’SU’)
WHERE Run.DateCreated BETWEEN FromDate AND ToDate

fn1

In this example, we are retrieving all process runs that were created this week (@ptd=1, period to date), with the week (@period=’W’) ending on Sunday (@week_end_day=’SU’). Since today is Saturday April 14th, the FromDate/ToDate will be the last Monday April 9th to today April 14th.

SELECT *
FROM dbo.cdmsRun
CROSS JOIN dbo.fnGetDateRange(getdate(),’W’,1,’SU’)
WHERE cdmsRun.DateCreated BETWEEN FromDate AND ToDate

fn2

You will notice a table variable @daysToSubtract; this is used instead of the DATEPART(weekday, @date) function… The return value of datepart, when using week or weekday, is set by using SET DATEFIRST. This cannot be done within the function itself, so I came up with a ‘dirty’ solution to get the appropriate number of days to subtract from the @date being passed in, in order to compute the FromDate.

———————————————————————————————

IF OBJECT_ID(‘dbo.fnGetDateRange’) IS NOT NULL
DROP FUNCTION dbo.fnGetDateRange
GO
CREATE FUNCTION dbo.fnGetDateRange
(@date DATE  — date to evaluate
,@period NCHAR(1) = ‘W’ — W=week, M=month, Q=quarter, Y=year => default: week
,@ptd BIT = 0  — period to date, otherwise last full period => default: last full
,@week_end_day NCHAR(2) = ‘SU’ — MO=monday, TU=tuesday, WE=wednesday, TH=thursday, FR=friday, SA=saturday, SU=sunday
)
RETURNS @t TABLE (FromDate DATE, ToDate DATE)
AS

BEGIN

DECLARE @FromDate DATE = NULL
DECLARE @ToDate DATE = NULL

IF (@period = ‘W’)
BEGIN

DECLARE @days INT
DECLARE @curr_day NVARCHAR(12) = DATENAME(WEEKDAY, @date)
DECLARE @daysToSubtract AS TABLE (EndDay NCHAR(2), MO INT, TU INT, WE INT, TH INT, FR INT, SA INT, SU INT)

INSERT INTO @daysToSubtract (EndDay,MO,TU,WE,TH,FR,SA,SU)
SELECT ‘MO’,0,-1,-2,-3,-4,-5,-6
UNION
SELECT ‘TU’,-6,0,-1,-2,-3,-4,-5
UNION
SELECT ‘WE’,-5,-6,0,-1,-2,-3,-4
UNION
SELECT ‘TH’,-4,-5,-6,0,-1,-2,-3
UNION
SELECT ‘FR’,-3,-4,-5,-6,0,-1,-2
UNION
SELECT ‘SA’,-2,-3,-4,-5,-6,0,-1
UNION
SELECT ‘SU’,-1,-2,-3,-4,-5,-6,0

SELECT @days =
CASE @curr_day
WHEN ‘Monday’ THEN MO
WHEN ‘Tuesday’ THEN TU
WHEN ‘Wednesday’ THEN WE
WHEN ‘Thursday’ THEN TH
WHEN ‘Friday’ THEN FR
WHEN ‘Saturday’ THEN SA
WHEN ‘Sunday’ THEN SU
END
FROM @daysToSubtract
WHERE EndDay = @week_end_day

SET @ToDate = DATEADD(DAY, @days, @date)
SET @FromDate = DATEADD(DAY, -6, @ToDate)

IF (@ptd = 1)
BEGIN
IF (@days = 0) SET @days = -6 ELSE SET @days = 1

SET @FromDate = DATEADD(DAY, @days, @ToDate)
SET @ToDate = @date
END
END

ELSE IF (@period = ‘M’)
BEGIN
IF (@ptd = 0)
BEGIN
SET @FromDate = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,@date)), 0)
SET @ToDate = DATEADD(ms,-2,DATEADD(mm, DATEDIFF(mm,0,@date), 0))
END
ELSE
BEGIN
SET @FromDate = DATEADD(mm, DATEDIFF(mm,0,@date), 0)
SET @ToDate = @date
END
END

ELSE IF (@period = ‘Q’)
BEGIN
IF (@ptd = 0)
BEGIN
SET @FromDate = DATEADD(qq, DATEDIFF(qq,0,DATEADD(qq,-1,@date)), 0)
SET @ToDate = DATEADD(ms,-2,DATEADD(qq, DATEDIFF(qq,0,@date), 0))
END
ELSE
BEGIN
SET @FromDate = DATEADD(qq, DATEDIFF(qq,0,@date), 0)
SET @ToDate = @date
END
END

ELSE IF (@period = ‘Y’)
BEGIN

IF (@ptd = 0)
BEGIN
SET @FromDate = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy,-1,@date)), 0)
SET @ToDate = DATEADD(ms,-2,DATEADD(yy, DATEDIFF(yy,0,@date), 0))
END
ELSE
BEGIN
SET @FromDate = DATEADD(yy, DATEDIFF(yy,0,@date), 0)
SET @ToDate = @date
END

END

INSERT INTO @t (FromDate, ToDate)
VALUES (@FromDate,@ToDate)

RETURN

END

———————————————————————————————

Here are the test results:

declare @date date = getdate()

select 1, *, ‘MTD’ as Period from dbo.fnGetDateRange(@date,’M’,1,default)
UNION
select 2, *, ‘PriorMonth’ as Period from dbo.fnGetDateRange(@date,’M’,0,default)
UNION
select 3, *, ‘QTD’ as Period from dbo.fnGetDateRange(@date,’Q’,1,default)
UNION
select 4, *, ‘PriorQuarter’ as Period from dbo.fnGetDateRange(@date,’Q’,0,default)
UNION
select 5, *, ‘YTD’ as Period from dbo.fnGetDateRange(@date,’Y’,1,default)
UNION
select 6, *, ‘PriorYear’ as Period from dbo.fnGetDateRange(@date,’Y’,0,default)
UNION
select 7, *, ‘WTD’ as Period from dbo.fnGetDateRange(@date,’W’,1,’SU’)
UNION
select 8, *, ‘PriorWeek’ as Period from dbo.fnGetDateRange(@date,’W’,0,’SU’)

fn3