Passing Date Range to Stored Procedure

[briangriffin]

Does anyone have a creative way of passing a date range to a SQL Server 2005 stored procedure? I thought something like this might work as a command object:

declare @min smalldatetime, @max smalldatetime

set @min = minimum({?Date Range}),
@max = maximum({?Date Range})

exec spname @min, @max

{/code]

That won't work for me, though. The user doesn't want to use two parameters, Start Date and End Date, if he doesn't absolutely have to.

Thanks.

 

[whitakaster]

Been there, done that!!

We use a user defined table-valued function in SQL Server to translate a date range phrase, i.e. MTD, YTD, PRIOR 30 DAYS, LAST MONTH, LAST QUARTER, etc. and return StartDate and EndDate fields back to the report's stored proc to filter with.

So, the crystal report sees the stored proc's @DateRangePhrase parameter, then populate @DateRangePhrase parameter in the RPT with a static list... MTD, YTD, PRIOR 30 DAYS, LAST MONTH, LAST QUARTER, etc. We also offer 2 additional "override" start and end date parameters in the stored proc, to still allow for custom date ranges if needed, but it's ignored if null and the Date Range Phrase is selected.

This will allow the user to only have to select one parameter phrase like YTD. This also enables you to set up a recurring schedule based on "YTD", for example. Even more importantly this allows you to centralize your business logic for determining these date ranges in a reusable UDF SQL Server function, which greatly reduces future maintenance.

Sample code from the UDF:
INSERT INTO @tDates
SELECT
[StartDate] =
CASE WHEN @pOverrideStartDate IS NOT NULL THEN DATEADD(dd, DATEDIFF(dd, 0, @pOverrideStartDate), 0) ELSE
CASE @pDateRangePhrase
WHEN 'TODAY' THEN @vToday
WHEN 'YESTERDAY' THEN DATEADD(dd,-1,@vToday)
WHEN 'WTD' THEN DATEADD(wk,DATEDIFF(wk,0,@vToday),-1) -- STARTS Sunday (unless billing)
WHEN 'MTD' THEN DATEADD(mm,DATEDIFF(mm,0,@vToday), 0)
WHEN 'QTD' THEN
CAST((CAST(YEAR(@vToday) as varchar(4)) +
CASE
WHEN MONTH(@vToday) in (1,2,3) THEN '/01/01'
WHEN MONTH(@vToday) in (4,5,6) THEN '/04/01'
WHEN MONTH(@vToday) in (7,8,9) THEN '/07/01'
WHEN MONTH(@vToday) in (10,11,12) THEN '/10/01'
END) as datetime)
WHEN 'YTD' THEN DATEADD(yy, DATEDIFF(yy,0,@vToday), 0)
---
WHEN 'THIS WEEK' THEN DATEADD(wk, DATEDIFF(wk,0,@vToday),-1)
WHEN 'THIS MONTH' THEN DATEADD(mm, DATEDIFF(mm,0,@vToday), 0)
WHEN 'THIS QUARTER' THEN
CAST((CAST(YEAR(@vToday) as varchar(4)) +
CASE
WHEN MONTH(@vToday) in (1,2,3) THEN '/01/01'
WHEN MONTH(@vToday) in (4,5,6) THEN '/04/01'
WHEN MONTH(@vToday) in (7,8,9) THEN '/07/01'
WHEN MONTH(@vToday) in (10,11,12) THEN '/10/01'
END) as datetime)
WHEN 'THIS YEAR' THEN DATEADD(yy, DATEDIFF(yy,0,@vToday), 0)
---
WHEN 'LAST WEEK' THEN DATEADD(wk,DATEDIFF(wk,7,@vToday),-1)
WHEN 'LAST MONTH' THEN DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,@vToday),0))
WHEN 'LAST QUARTER' THEN
CASE
WHEN DATEPART(MONTH,@vToday) IN (1,4,7,10) THEN
CAST(CAST(DATEPART( MONTH,DATEADD(MONTH,-3,@vToday) ) as varchar(2)) + '/1/' +
CAST(DATEPART( YEAR,DATEADD(MONTH,-3,@vToday) ) as varchar(4)) as datetime)
WHEN DATEPART(MONTH,@vToday) IN (2,5,8,11) THEN
CAST(CAST(DATEPART( MONTH,DATEADD(MONTH,-4,@vToday) ) as varchar(2)) + '/1/' +
CAST(DATEPART( YEAR,DATEADD(MONTH,-4,@vToday) ) as varchar(4)) as datetime)
WHEN DATEPART(MONTH,@vToday) IN (3,6,9,12) THEN
CAST(CAST(DATEPART( MONTH,DATEADD(MONTH,-5,@vToday) ) as varchar(2)) + '/1/' +
CAST(DATEPART( YEAR,DATEADD(MONTH,-5,@vToday) ) as varchar(4)) as datetime)
END
WHEN 'LAST YEAR' THEN DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,@vToday),0))
---
WHEN 'PRIOR 30 DAYS' THEN DATEADD(dd,-30,@vToday)
WHEN 'PRIOR 60 DAYS' THEN DATEADD(dd,-60,@vToday)
WHEN 'PRIOR 90 DAYS' THEN DATEADD(dd,-90,@vToday)
WHEN 'PRIOR 6 MONTHS' THEN DATEADD(mm,-6,@vToday)
---
WHEN 'Q1' THEN CAST((CAST(YEAR(@vToday) as varchar(4)) + '/01/01') as datetime)
WHEN 'Q2' THEN CAST((CAST(YEAR(@vToday) as varchar(4)) + '/04/01') as datetime)
WHEN 'Q3' THEN CAST((CAST(YEAR(@vToday) as varchar(4)) + '/07/01') as datetime)
WHEN 'Q4' THEN CAST((CAST(YEAR(@vToday) as varchar(4)) + '/10/01') as datetime)
END
END
,[EndDate] =
CASE WHEN @pOverrideEndDate IS NOT NULL THEN DATEADD(ms,-3,DATEADD(dd,1,@pOverrideEndDate)) ELSE
CASE @pDateRangePhrase
WHEN 'TODAY' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'YESTERDAY' THEN DATEADD(ms,-3,@vToday)
WHEN 'WTD' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'MTD' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'QTD' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'YTD' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
---
WHEN 'THIS WEEK' THEN DATEADD(ms,-3,DATEADD(wk,DATEDIFF(wk,0,@vToday),6)) --ENDS Saturday
WHEN 'THIS MONTH' THEN DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@vToday)+1,0)))
WHEN 'THIS QUARTER' THEN
CASE
WHEN MONTH(@vToday) in (1,2,3) THEN DATEADD(ms,-3,CAST((CAST(YEAR(@vToday) as varchar(4)) + '/04/01') as datetime))
WHEN MONTH(@vToday) in (4,5,6) THEN DATEADD(ms,-3,CAST((CAST(YEAR(@vToday) as varchar(4)) + '/07/01') as datetime))
WHEN MONTH(@vToday) in (7,8,9) THEN DATEADD(ms,-3,CAST((CAST(YEAR(@vToday) as varchar(4)) + '/10/01') as datetime))
WHEN MONTH(@vToday) in (10,11,12) THEN DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@vToday)+1,0)))
END
WHEN 'THIS YEAR' THEN DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@vToday)+1,0)))
---
WHEN 'LAST WEEK' THEN DATEADD(ms,-3,DATEADD(wk,DATEDIFF(wk,7,@vToday),6))
WHEN 'LAST MONTH' THEN DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@vToday), 0))
WHEN 'LAST QUARTER' THEN
CASE
WHEN DATEPART(MONTH,@vToday) IN (1,4,7,10) THEN
DATEADD(ms,-3,(CAST(CAST(DATEPART( MONTH,DATEADD(MONTH,0,@vToday) ) as varchar(2)) + '/1/' +
CAST(DATEPART( YEAR,DATEADD(MONTH,0,@vToday) ) as varchar(4)) as datetime)))
WHEN DATEPART(MONTH,@vToday) IN (2,5,8,11) THEN
DATEADD(ms,-3,(CAST(CAST(DATEPART( MONTH,DATEADD(MONTH,-1,@vToday) ) as varchar(2)) + '/1/' +
CAST(DATEPART( YEAR,DATEADD(MONTH,0,@vToday) ) as varchar(4)) as datetime)))
WHEN DATEPART(MONTH,@vToday) IN (3,6,9,12) THEN
DATEADD(ms,-3,(CAST(CAST(DATEPART( MONTH,DATEADD(MONTH,-2,@vToday) ) as varchar(2)) + '/1/' +
CAST(DATEPART( YEAR,DATEADD(MONTH,0,@vToday) ) as varchar(4)) as datetime)))
END
WHEN 'LAST YEAR' THEN DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@vToday),0)))
---
WHEN 'PRIOR 30 DAYS' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'PRIOR 60 DAYS' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'PRIOR 90 DAYS' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
WHEN 'PRIOR 6 MONTHS' THEN DATEADD(ms,-3,DATEADD(dd,1,@vToday))
---
WHEN 'Q1' THEN DATEADD(ms,-3,CAST((CAST(YEAR(@vToday) as varchar(4)) + '/04/01') as datetime))
WHEN 'Q2' THEN DATEADD(ms,-3,CAST((CAST(YEAR(@vToday) as varchar(4)) + '/07/01') as datetime))
WHEN 'Q3' THEN DATEADD(ms,-3,CAST((CAST(YEAR(@vToday) as varchar(4)) + '/10/01') as datetime))
WHEN 'Q4' THEN DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@vToday)+1,0)))
END
END
-----------------------------------------

Then you call this function in the report's stored proc:
SELECT
@StartDate = StartDate
,@EndDate = EndDate
FROM
dbo.udf_cr_DateRangePhraseTranslator(
'PRIOR 60 DAYS' --@pDateRangePhrase varchar(50)
,null --@pOverrideStartDate datetime
,null --@pOverrideEndDate datetime
,'' --@pPlaceHolder1 varchar(50)
,'' --@pPlaceHolder2 varchar(50)
,'' --@pPlaceHolder3 varchar(50)
,'' --@pPlaceHolder4 varchar(50)
,'' --@pPlaceHolder5 varchar(50)
)

-----------------------------------------

Good luck!