I touched on Reporting Services Date Parameters, but what I did not cover is the underlying importance of understanding how TSQL works in terms of date parameters. You need to know that there is an implicit midnight time-stamp of 00:00:00 when a date is only supplied. So when you are dealing with date parameters in terms of FROM and TO dates, the TO will need or should include all records for that date as well. However, since a date is only supplied your chances of including all records for that end date are very slim. I was able to correct this by adding a modifier in stored procedures that takes the input date and increments it by one day.
For illustration purposes here are some TSQL scripts that will allow you to test a few queries to see what is actually begin returned when you only pass a date to a DATETIME field. This will help you understand what the results of your report will be as well.
Step 1: Create Table
CREATE TABLE #TestDate ( [ID] [int] IDENTITY(1,1) NOT NULL, [StandardDT] [datetime] NOT NULL, [MyKey] [int] NOT NULL, [Notes] [nvarchar](100) NULL, )
Step 2: Populate Table
INSERT INTO #TestDate (MyKey, StandardDT, Notes) SELECT 1, '1/1/2010', 'Note 1' UNION ALL SELECT 2, '2/1/2010', 'Note 2' UNION ALL SELECT 3, '3/1/2010', 'Note 3' UNION ALL SELECT 4, '4/1/2010 10:42:22', 'Note 4' UNION ALL SELECT 5, '4/1/2010 22:42:22', 'Note 5' UNION ALL SELECT 6, '6/1/2010', 'Note 6' UNION ALL SELECT 7, '7/1/2010', 'Note 7' UNION ALL SELECT 8, '7/1/2010 01:10:19', 'Note 8' UNION ALL SELECT 9, '7/1/2010 02:11:20', 'Note 9' UNION ALL SELECT 10, '7/1/2010 21:21:22', 'Note 10'
Step 3: Verify Data
SELECT * FROM #TestDate
As you can see we have 10 records total with some records that have a time-stamp associated that are beyond midnight specifically April and July while the the other months are midnight.
Step 4: Test Queries
Let’s run through some test queries to help better illustrate my point. Let’s query the data using a FROM date of 3/1/2010 and a TO date of 4/1/2010. Looking at the data there is one record for 3/1/2010 and two records from 4/1/2010. How many records do you think will be returned? Well let’s just see for ourselves.
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = '3/1/2010' SET @EndDate = '4/1/2010' SELECT * FROM #TestDate WHERE StandardDT BETWEEN @StartDate AND @EndDate SELECT @StartDate SELECT @EndDate
Interesting… there is only one record returned even though 4/1/2010 is within our query parameters. Take a look at lines 8 & 9 from the query above and pay attention to the time-stamps. You can see in the screen cast below that the TO date which is represented by @EndDate parameter is 4/1/2010 00:00:00 which is 4/1/2010 midnight. Well looking through the data proves we do not have any records that meet that specific criteria because there are no records for 4/1/2010 that have a midnight time-stamp associated.
Let’s try a few more test queries just to get a clearer picture. Let’s see how many records are returned when we search FROM 7/1/2010 TO 7/1/2010. We know that our data set contains four records for 7/1/2010, but can you guess how many records will be returned if we want to search FROM 7/1/2010 TO 7/1/2010?
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = '7/1/2010' SET @EndDate = '7/1/2010' SELECT * FROM #TestDate WHERE StandardDT BETWEEN @StartDate AND @EndDate SELECT @StartDate SELECT @EndDate
There was only one record returned because there was only one record that met the criteria 100%. If you look at the values for @StartDate and @EndDate they both are returning 7/1/2010 00:00:00.
Now let’s run through one last query, because practice makes perfect. If I were to search our data set for all records from 4/1/2010 and supply a FROM date of 4/1/2010 and a TO date of 4/1/2010 how many records would be returned? The answer is zero, because both you and I know there are no records within the data set that have a time-stamp of 00:00:00 for 4/1/2010.
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = '4/1/2010' SET @EndDate = '4/1/2010' SELECT * FROM #TestDate WHERE StandardDT BETWEEN @StartDate AND @EndDate SELECT @StartDate SELECT @EndDate
Perfect. No records returned just as we thought.
Step 5: Solution
The easiest way to rectify this is to ask the end users to use the next day, but that is not a practical answer because you and I both know after the first weekend or vacation the end users will return to their normal mode and begin searching through the data using the date they did before. So in order to satisfy the requirements I decided to add a parameter modifier to my procedure which takes the received input value for the @EndDate and increments the day by one. Meaning if 7/1/2010 were passed as the TO or @EndDate value it would become 7/2/2010 00:00:00 and all records from 7/1/2010 will be returned. Pretty simple.
ALTER PROCEDURE sp_RP_MyReport ( @StartDate DATETIME ,@EndDate DATETIME ) AS SET @EndDate = DATEADD(d,1,@EndDate)
Line #: 9 in the above syntax is what I implemented which makes the adjustment to the @EndDate parameter.
Step 6: Validate Solution
Let’s add the modifier just below the parameter declarations and above the SELECT statement and see what we get when we execute the script. Can you guess what will be returned?
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = '3/1/2010' SET @EndDate = '4/1/2010' SET @EndDate = DATEADD(d,1,@EndDate) SELECT * FROM #TestDate WHERE StandardDT BETWEEN @StartDate AND @EndDate SELECT @StartDate SELECT @EndDate
Success! Three records returned. By adding the modifier (Line #: 5) the value was accepted, incremented by 1 and supplied back to the SELECT statement and the records which fell into the criteria were returned.
Step 7: Cleanup
Now that we are all squared away, let’s try not forget that we still need to drop the temp table.
DROP TABLE #TestDate
You can always use the DATEADD in the BETWEEN statement like below in line 06.
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = '3/1/2010' SET @EndDate = '4/1/2010' SELECT * FROM #TestDate WHERE StandardDT BETWEEN @StartDate AND DATEADD(d,1,@EndDate)
Being the guy that I am I often try to implement pure TSQL solutions especially when working with parameters via Reporting Services. I know it seems like they go hand-in-hand and to a great extent they do, but there are some cases where it is best to use expressions for the report parameters over a stored procedure. Don’t get me wrong my TSQL approach worked fine but when I used the expression I was able to accomplish the exact same results with slightly less syntax to write and possibly less overhead. I stumbled upon the blog post “Calculating the first and last day of the month” by datageek on blogspot which illustrates the expressions I used for my report.
SELECT DATEADD(Month,DATEDIFF(Month,0,getdate()),-1)+1 'StartDate' SELECT DATEADD(Month,DATEDIFF(Month,0,getdate())+1,-1) 'EndDate'
RS Expression Syntax
Note: The following can be used for Access as well to get the first and last days of the month from what I understand.
First Day of the Month
=DateSerial(Year(Now), Month(Now), 1)
Last Day of the Month
=DateSerial(Year(Now), Month(Now) + 1, 0)
So for illustration purposes I put together a report based on the AdventureWorks database and used the RS Expression Syntax to populate the date fields, but first let’s create the stored procedure.
CREATE PROCEDURE sp_RP_GetEmployeeData ( @StartDate datetime ,@EndDate datetime ) AS SELECT e.EmployeeKey --, e.ParentEmployeeKey --, e.EmployeeNationalIDAlternateKey --, e.ParentEmployeeNationalIDAlternateKey --, e.SalesTerritoryKey , e.FirstName , e.LastName --, e.MiddleName --, e.NameStyle , e.Title , e.HireDate --, e.BirthDate --, e.LoginID --, e.EmailAddress , e.Phone --, e.MaritalStatus --, e.EmergencyContactName --, e.EmergencyContactPhone , e.SalariedFlag --, e.Gender --, e.PayFrequency , e.BaseRate --, e.VacationHours --, e.SickLeaveHours --, e.CurrentFlag --, e.SalesPersonFlag --, e.DepartmentName --, e.StartDate , e.EndDate --, e.Status --, st.SalesTerritoryCountry --, st.SalesTerritoryRegion --, st.SalesTerritoryGroup FROM AdventureWorksDW2008.dbo.DimEmployee AS e JOIN AdventureWorksDW2008.dbo.DimSalesTerritory AS st ON e.SalesTerritoryKey = st.SalesTerritoryKey WHERE (HireDate BETWEEN @StartDate AND @EndDate) ORDER BY e.HireDate
Now that we have the sProc in place building the report is pretty much the same for the most part; however, the report parameters have relocated. They are now found on the left hand side under the report data section. In 2005 you would have found the parameters under the Report menu.
Now to set default values for the parameters
1) Right click on the StartDate parameter and select “Parameter Properties”
2) At the Properties screen click on “Default Values”
3) Then select “Specify Values”
4) Click on the “Expression” button
5) Use the RS Expression Syntax for the “First Day of the Month”
6) Now follow the steps 1-5 and set the default value for the EndDate parameter but this time use the RS Syntax for the “Last Day of the Month”
7) Let’s preview the report, there won’t be any data though but the parameters are defaulting as intended
To see the data simply change the date range to reflect 1/1/1996 to 7/1/1999 then press “View Report”
1) If you just want to see the date only and not the time just right click on the field and select “Text Box Properties”
2) In the Text Box Properties dialog select Number
3) From within the Category section select “Date”
4) Then select the format under “Type”
And here is the outcome…