This week I was tasked with testing a stored procedure that was meant to output data on certain days. This was over a 120 day period, so I wanted to find some automated way of doing this, rather than changing the server date manually for each execution. The method I came up with involves the use of xp_cmdshell to execute the date command. Here’s an illustration of what I came up with.

First create this stored procedure. This will just check to see if it’s Saturday and output “Yes” if it is.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rhys Campbell
-- Create date: 2009-11-14
-- Description:	Outputs "Yes" if it is Saturday
-- otherwise "No"
-- =============================================
CREATE PROCEDURE usp_isItSaturday
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @isItSaturday VARCHAR(3);

    	SET @isItSaturday = CASE
				WHEN DATEPART(dw, GETDATE()) = 7 THEN 'Yes'
				ELSE
					'No'
			    END;
	SELECT GETDATE(), @isItSaturday;

END
GO

The script below will change the date by one day, then execute usp_isItSaturday, in sequence up to the end of 2009. I had to introduce the WAITFOR delay because SQL Server seems to take a few seconds to register the date change. The script will simply loop until the day changes before executing the store procedure.

-- Enable xp_cmdshell
EXEC master.dbo.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

DECLARE @command VARCHAR(4000),
		@date VARCHAR(10),
		@starting DATETIME,
		@day INTEGER;

SET @starting = GETDATE();

WHILE (@starting <= '2009-12-31T00:00:00')
BEGIN

	SET @date = CAST(DATEPART(d, @starting) AS VARCHAR(2)) + '/' + CAST(DATEPART(m, @starting) AS VARCHAR(2)) + '/' + CAST(DATEPART(YYYY, @starting) AS VARCHAR(4));
	-- Get the day for tracking when the date change has taken effect
	SET @day = DATEPART(d, @starting);
	SET @command = 'date ' + @date;
	EXEC xp_cmdshell @command, no_output;

	-- SQL Server takes a while to pickup the date change
	-- Loop around until the day has changed before executing the proc
	WHILE(@day <> DATEPART(d, GETDATE()))
	BEGIN
		WAITFOR DELAY '00:00:01';
	END

	-- Call the procedure with the changed date
	EXEC dbo.usp_isItSaturday;
	-- Increment date by 1 day
	SET @starting = DATEADD(d, 1, @starting);

END

-- Disable xp_cmdshell
EXEC master.dbo.sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

Here’s a sample of the output. You can see it has correctly identified the days that are Saturday.

usp_isItSaturday