Anyone who has worked with SQL Server Reporting Services (SSRS) for any appreciable amount of time has run into the issue of date range parameters.  SSRS appears to have no easy way to use one parameter to specify both a beginning and ending date.

If you join to a dates table, there is no dynamicity to that table and no way to specify fluid date ranges, e.g., "This Calendar Quarter to Date."  Do you want to rebuild a table every day to keep the date ranges correct?  Is that the best way to do this? Usually not, which usually means that you end up specifying report beginning and ending dates as two separate parameters.  It is frustrating enough to drive most DBA insane (or is that more insane).  

Good news!  There is a way around this restriction, and this article shows you how to do it easily.

Use a Dynamically-Populated View

What you do is create a view that calculates date values on-the-fly using system functions.  Such a view allows joining and control of date ranges without constantly updating a table or forcing your customers to choose beginning and end dates for date ranges, some of which need calculation and may result in incorrect parameters (and reports with incorrect data).

We will create a three-column view with the following columns:

• Period - A VARCHAR(100) column that contains a description of the period
• Begin Date - A DATETIME column that contains the start date of the period, with time set to midnight.
• End Date - A DATETIME column that contains the ending date of the period, with time set to midnight.

We will assume a fiscal year different from the calendar year, starting on October 1.  Here is a sample date range view:

Create view [V_DATE_RANGE] as
select 
Convert(varchar(100),'Year To Date') as [Period]
,convert(datetime,'1/1/' + cast(year(getdate()) as varchar(4))) as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Today') as [Period]
,convert(datetime,convert(date,getdate())) as BeginDate
,convert(datetime,convert(date,getdate())) as EndDateunion
union all
select 
Convert(varchar(100),'Yesterday') as [Period]
,convert(datetime,convert(date,getdate()))-1 as BeginDate
,convert(datetime,convert(date,getdate()))-1 as EndDate
union all
select 
Convert(varchar(100),'Fiscal Year To Date') as [Period]
,convert(datetime,'10/1/' + cast(year(getdate()) as varchar(4))) as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Month To Date') as [Period]
,convert(datetime,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4))) as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last Month') as [Period]
,convert(datetime,(dateadd(MM,-1,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4))))) as BeginDate
,convert(datetime,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4)))-1 as EndDate
union all
select 
Convert(varchar(100),'Last Month to Date') as [Period]
,convert(datetime,(dateadd(MM,-1,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4))))) as BeginDate
,convert(datetime,(dateadd(MM,-1,cast(month(getdate()) as varchar(4)) + '/' + cast(day (getdate()) as varchar(4)) + '/' + cast(year(getdate()) as varchar(4))))) as EndDate
union all
select 
Convert(varchar(100),'Last Year') as [Period]
,convert(datetime,'1/1/' + cast(year(getdate())-1 as varchar(4))) as BeginDate
,convert(datetime,'1/1/' + cast(year(getdate()) as varchar(4)))-1 as EndDate
union all
select 
Convert(varchar(100),'Last Year to Date') as [Period]
,convert(datetime,'1/1/' + cast(year(getdate())-1 as varchar(4))) as BeginDate
,dateadd(yy,-1,convert(datetime,convert(date,getdate()))) as EndDate
union all
select 
Convert(varchar(100),'Last 7 days') as [Period]
,convert(datetime,convert(date,getdate()))-7 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last 14 days') as [Period]
,convert(datetime,convert(date,getdate()))-14 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last 21 days') as [Period]
,convert(datetime,convert(date,getdate()))-21 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last 28 days') as [Period]
,convert(datetime,convert(date,getdate()))-28 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last 30 days') as [Period]
,convert(datetime,convert(date,getdate()))-30 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last 60 days') as [Period]
,convert(datetime,convert(date,getdate()))-60 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last 90 days') as [Period]
,convert(datetime,convert(date,getdate()))-90 as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'This Calendar Quarter') as [Period]
,DATEADD(qq, DATEDIFF(q,0,GETDATE()),0) as BeginDate
,DATEADD(qq, DATEDIFF(q,0,GETDATE())+1,0)-1 as EndDate
union all
select 
Convert(varchar(100),'This Calendar Quarter to Date') as [Period]
,DATEADD(qq, DATEDIFF(q,0,GETDATE()),0) as BeginDate
,convert(datetime,convert(date,getdate())) as EndDate
union all
select 
Convert(varchar(100),'Last Calendar Quarter') as [Period]
,DATEADD(qq, DATEDIFF(q,2,GETDATE())-1,0) as BeginDate
,DATEADD(qq, DATEDIFF(q,2,GETDATE()),0)-1 as EndDate
union all
select 
Convert(varchar(100),'Last Calendar Quarter to Date') as [Period]
,DATEADD(qq, DATEDIFF(q,0,GETDATE())-1,0) as BeginDate
,DATEADD(QQ,-1,convert(datetime,convert(date,getdate()))) as EndDate
union all
select 
Convert(varchar(100),'Last Year Calendar Quarter') as [Period]
,DATEADD(yyyy,-1,DATEADD(qq, DATEDIFF(q,2,GETDATE()),0)) as BeginDate
,DATEADD(yyyy,-1,DATEADD(qq, DATEDIFF(q,2,GETDATE())+1,0)-1) as EndDateunion
union all
select 
Convert(varchar(100),'Last Year Calendar Quarter to Date') as [Period]
,DATEADD(yyyy,-1,DATEADD(qq, DATEDIFF(q,2,GETDATE()),0)) as BeginDate
,DATEADD(yyyy,-1,convert(datetime,convert(date,getdate()))) as EndDate
union all
select 
Convert(varchar(100),'Next Year') as [Period]
,Convert(datetime,'1/1/' + cast(YEAR(GETDATE())+1 as varchar(4))) as BeginDate
,Convert(datetime,'12/31/' + cast(YEAR(GETDATE())+1 as varchar(4)))  as EndDate
union all
select 
Convert(varchar(100),'Next Year to Date') as [Period]
,Convert(datetime,'1/1/' + cast(YEAR(GETDATE())+1 as varchar(4))) as BeginDate
,Convert(datetime,cast(MONTH(GETDATE()) as varchar(2)) + '/' + cast(DAY(GETDATE()) as varchar(2)) + '/' + cast(YEAR(GETDATE())+1 as varchar(4)))  as EndDate
GO

I've attached the SQL for the above date range view to this article.

Selecting from this view on October 21, 2015 yields this:

You can build in as many or as few date ranges as you need.  You can create your own custom date ranges as your business needs require.  What if your customer needs a new date range that you haven't thought to do?  No problem.  Adding a date range requires altering the view to add the new date range.  As long as no two date range names are duplicates, the sky is the limit.

Caveats:  

• More than about 50 date ranges makes the view unwieldy for customers.
• You cannot index this view on the period name since there is no underlying physical table.
• The syntax I've used includes the DATE data type, which first came into being in SQL Server 2008.  You would have to adjust the view for SQL Server 2005 or (yipes!) 2000.
• The syntax I've used does not use newer (2012+) date functions such as DATEFROMPARTS, nor does it use newer types such as DATETIME2 or DATETIMEOFFSET.  In my shop I still have some SQL 2008 compatibility needs.  However, if you wish to adapt to these newer functions or types, the work should be simple.

Build a Parameter that Uses the View

The view executes rapidly and without disk I/O.  You then, inside of Visual Studio's report designer, create a dataset like this:

The next step is to create a parameter named "Period", which is fed from that dataset:

Join the View Into the Report Details Dataset

To get your report data, here is the general form of your data query:

select
   [columns]
from [Some Table]
INNER JOIN V_DATE_RANGE DR 
    ON DR.Period = @Period
    and [beginning date] >= DR.BeginDate
    and [ending Date] < DR.EndDate+1 -- the "+1" lets you have a date that displays nicely in the headers of your report

When you run your report, you will have a parameter choice:

You also will have the ability to place these dataset fields in the detail or group headings of the report to show the range selected.  Here is an example lifted from an actual report:

Finally, you have the ability to schedule a subscription to the report by any of these date ranges, and they will be correct in real-time when the report is run.

Conclusion

A little perserverance can make you a hero in the eyes of your customers.  Make their SSRS reporting experience just a little better with a simple view-based date range parameter.

John F. Tamburo is the Chief Database Administrator for Landauer, Inc., the world's leading authority on radiation measurement, physics and education. John can be found at @SQLBlimp on Twitter.  John also blogs at www.sqlblimp.com.