Date Parameters and Things to Consider

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

Side Note


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)

Using xp_cmdshell to pass parameters to vbs

For an existing client running SQL Server 2005 Standard there was a need to take values from a result set and pass them to a vbs that resided on the server. In my post “Formating Date and Time” I ran through the steps of formatting a datetime data type to meet my needs.

I chose to pursue a pure tsql based option but there are other avenues which I could have followed such as using a pure VBS approach recommended by Robert Davis (blog | @sqlsoldier) which I have done in the past and worked quite well. Not sure why it didn’t come to mind as it is a viable solution first. Then Dave Levy (blog | @Dave_Levy) suggested powerShell. Which is something I actually haven’t worked with yet. Interesting as #powerShell sounds I had to produce something ASAP, but in the process I would research #powerShell nonetheless.

So I chose a TSQL path using the extended stored procedure xp_cmdshell to accomplish the task at hand. To get started xp_cmdshell needs to be enabled, but first you might want to turn on advanced options to see if its is already on.

TSQL

EXECUTE SP_CONFIGURE

Outcome
StandardOptions

As you can see the results set are limited so in order to see information about xp_cmdshell we need to switch on the advanced options… so let’s get to it.

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE
GO

Being in alphabetic order you’ll need to scroll all the way down to see if the config_value is set to one. If it is set to zero then it’s disabled. In that event we need to enable it.

Outcome
02.AdvOptions_v0.01

That is done by setting the value to one. This is simply done by running the following:

EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE
GO

Now that xp_cmdshell is enabled we will see a value of one in the config_value field.

Outcome
03.xp_cmdshell_enabled

To test it let’s run the following:

DECLARE @results INT
DECLARE @cmd nvarchar(255)
SET @cmd = 'ping sqlsamson8' + ' -n 1 | find /i "reply"'

EXEC @results = master..xp_cmdshell @cmd
IF (@results = 0)
	SELECT 'ONLINE'
ELSE
	SELECT 'OFFLINE'

I don’t have a system called sqlsamson8 so ‘OFFLINE’ is returned.

Outcome
04.TestRun

Now let’s test this with a system that does exist.

DECLARE @results INT
DECLARE @cmd nvarchar(255)
SET @cmd = 'ping sqlsam7' + ' -n 1 | find /i "reply"'

EXEC @results = master..xp_cmdshell @cmd
IF (@results = 0)
	SELECT 'ONLINE'
ELSE
	SELECT 'OFFLINE'

Sure enough it’s ‘ONLINE’.

Outcome
05.TestRun

This is cool but I need some form of log to be generated. In short a file needs to be created and written to if the device was found on or offline.

DECLARE @results INT, @onLog NVARCHAR(50),@offLog NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @mn NVARCHAR(50), @cmd1 NVARCHAR(255)

-- Output results to file
SET @onLog = 'D:\xp_cmdshell\Online.txt'
SET @offLog = 'D:\xp_cmdshell\Offline.txt'
-- set target computer name
SET @mn = 'sqlsamson8'
-- set print process
SET @cmd = 'ping ' + @mn + ' -n 1 | find /i "reply"'
-- Execute ping process
EXEC @results = master..xp_cmdshell @cmd
IF (@results = 0)
	BEGIN
		-- Display online message to grid results
		SELECT @mn + ' IS ONLINE!'
		-- If device is online log computer name to output file
		SET @cmd1 =  'echo ' + @mn + ' >> ' + @onLog
		-- Execute statement
		EXEC master..xp_cmdshell @cmd1
	END
ELSE
	BEGIN
		-- Display offline message to grid results
		SELECT @mn + ' IS OFFLINE'
		-- If device is offline log computer name to output file
		SET @cmd1 =  'echo ' + @mn + ' >> ' + @offLog
		-- Execute statement
		EXEC master..xp_cmdshell @cmd1
	END	

Outcome
06.TestRun_WithOutputLog

Perfect. Exactly what I needed. However I need to run through a series of devices and having to manually specify a computer name is out of the question. I could use a CURSOR, but today I feel in a WHILE loop kind of mode…

DECLARE @Cnt INT, @mn NVARCHAR(50), @newmn NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @results INT, @cmd1 NVARCHAR(255)
DECLARE @offLog NVARCHAR(50), @onLog NVARCHAR(50)

SET @onLog = 'D:\xp_cmdshell\ONLINE.txt'
SET @offLog = 'D:\xp_cmdshell\OFFLINE.txt'
SET @mn = 'sqlsam'
SET @Cnt = 0

WHILE @Cnt < 10
BEGIN
	SET @Cnt = @Cnt + 1
	-- take @mn and append @Cnt (i.e. sqlsam + 1 = sqlsam1)
	SET @newmn = @mn + CONVERT(VARCHAR,@Cnt)
	SET @cmd = 'ping ' + @newmn + ' -n 1 | find /i "reply"'
	EXEC @results = master..xp_cmdshell @cmd
		IF (@results = 0)
			BEGIN
				-- Display online message to grid results
				PRINT @newmn + ' IS ONLINE!'
				-- If device is online log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @onLog
				-- Execute statement
				EXEC master..xp_cmdshell  @cmd1
			END
		ELSE
			BEGIN
				-- Display offline message to grid results
				PRINT @newmn + ' IS OFFLINE'
				-- If device is offline log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @offLog
				-- Execute statement
				EXEC master..xp_cmdshell @cmd1
			END
END

Great… loop and ping though the devices and record to logs accordingly. Bingo!

Outcome
07.TestRunLoop_WithOutputLog

Well there is one problem… if I run this again the results will be appended to the existing log essentially doubling the results with the same data. This is completely avoidable if I append a date-time stamp to the log file names!

DECLARE @Cnt INT, @mn NVARCHAR(50), @newmn NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @results INT, @cmd1 NVARCHAR(255)
DECLARE @offLog NVARCHAR(50), @onLog NVARCHAR(50),@dtStamp NVARCHAR(50)

SET @dtStamp =	CONVERT(varchar, GETDATE(), 112) + '_' + 
								REPLACE(CONVERT(varchar(5), GETDATE(), 108),':','')

SET @onLog = 'D:\xp_cmdshell\ONLINE_' + @dtStamp +'.txt'
SET @offLog = 'D:\xp_cmdshell\OFFLINE_' + @dtStamp +'.txt'

SET @mn = 'sqlsam'
SET @Cnt = 0

WHILE @Cnt < 10
BEGIN
	SET @Cnt = @Cnt + 1
	-- take @mn and append @Cnt (i.e. sqlsam + 1 = sqlsam1)
	SET @newmn = @mn + CONVERT(VARCHAR,@Cnt)
	SET @cmd = 'ping ' + @newmn + ' -n 1 | find /i "reply"'
	EXEC @results = master..xp_cmdshell @cmd
		IF (@results = 0)
			BEGIN
				-- Display online message to grid results
				PRINT @newmn + ' IS ONLINE!'
				-- If device is online log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @onLog
				-- Execute statement
				EXEC master..xp_cmdshell  @cmd1
			END
		ELSE
			BEGIN
				-- Display offline message to grid results
				PRINT @newmn + ' IS OFFLINE'
				-- If device is offline log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @offLog
				-- Execute statement
				EXEC master..xp_cmdshell @cmd1
			END
END

Outcome
08.TestRunLoop_WithOutputLog_TimeStamp

Now I only need to add the VBS path, execute it and then we are golden!

DECLARE @Cnt INT, @mn NVARCHAR(50), @newmn NVARCHAR(50), @vbscmd NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @results INT, @cmd1 NVARCHAR(255)
DECLARE @offLog NVARCHAR(50), @onLog NVARCHAR(50),@dtStamp NVARCHAR(50)

SET @dtStamp =	CONVERT(varchar, GETDATE(), 112) + '_' + 
								REPLACE(CONVERT(varchar(5), GETDATE(), 108),':','')

SET @onLog =  'D:\xp_cmdshell\ONLINE_' + @dtStamp +'.txt'
SET @offLog =  'D:\xp_cmdshell\OFFLINE_' + @dtStamp +'.txt'


SET @mn = 'sqlsamson'
SET @Cnt = 0

WHILE @Cnt < 10
BEGIN
	SET @Cnt = @Cnt + 1
	-- take @mn and append @Cnt (i.e. sqlsam + 1 = sqlsam1)
	SET @newmn = @mn + CONVERT(VARCHAR,@Cnt)
	SET @cmd = 'ping ' + @newmn + ' -n 1 | find /i "reply"'
	EXEC @results = master..xp_cmdshell @cmd
		IF (@results = 0)
			BEGIN
			SET @vbscmd = 'cscript /nologo D:\test.vbs ' + '"' + @newmn + '"'
				-- Display online message to grid results
				PRINT @newmn + ' IS ONLINE!'
				-- If device is online log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @onLog
				-- Execute write statement
				EXEC master..xp_cmdshell @cmd1
				-- Execute vbs statement
				PRINT @vbscmd
				EXEC master..xp_cmdshell @vbscmd
			END
		ELSE
			BEGIN
				-- Display offline message to grid results
				PRINT @newmn + ' IS OFFLINE'
				-- If device is offline log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @offLog
				-- Execute statement
				EXEC master..xp_cmdshell @cmd1
			END
END

Outcome
09.ParameterPassedToVBS

Of course this only works when the naming convention is standardized and the numbers are sequentially sound. I could create a temp table to pull in all the device names and add a ID field that auto increments by a value of one. Then run through the loop selecting the data from temp table where ID = @Cnt. That is a feasible option.

Reporting Services Date Parameters

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.

TSQL Syntax

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.

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”

5-26-2010 8-16-31 AMa

2) At the Properties screen click on “Default Values”

5-26-2010 8-25-56 AM

3) Then select “Specify Values”

5-26-2010 8-41-10 AM

4) Click on the “Expression” button

5-26-2010 8-47-24 AM

5) Use the RS Expression Syntax for the “First Day of the Month”

5-26-2010 9-01-28 AM

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”

5-26-2010 9-07-08 AM

7) Let’s preview the report, there won’t be any data though but the parameters are defaulting as intended

5-26-2010 9-09-08 AM

To see the data simply change the date range to reflect 1/1/1996 to 7/1/1999 then press “View Report”

5-26-2010 9-15-54 AM

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”

5-26-2010 1-45-59 PM

2) In the Text Box Properties dialog select Number

5-26-2010 1-49-19 PM

3) From within the Category section select “Date”

5-26-2010 1-49-43 PM

4) Then select the format under “Type”

5-26-2010 1-50-09 PM

And here is the outcome…

5-26-2010 4-12-53 PM