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)