I really want to encourage people to automate these type of tests. I know that you said in the article about automating tests that “it is not a commonly accepted practice yet” and I think that may be because of the overhead involved by some of the automation frameworks. Here is a solution that is automated yet it is as simple to write as your manual tests. Once you've installed the TST database (http://tst.codeplex.com, once downloaded it takes about 20 seconds to install and it is all T-SQL) all you’d have to do for your case is to write the tests procedures below. Then you can execute the tests in command prompt by running:
"TST /RunAll DatabaseName"
or in SQL by running
"EXEC TST.Runner.RunAll DatabaseName"
CREATE PROCEDURE dbo.SQLTest_NearestBusinessDayOnOrBefore
AS BEGIN
DECLARE @ActualResult DATETIME
SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100827')
EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100827'')', {d '2010-08-27'}, @ActualResult
SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100828')
EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100828'')', {d '2010-08-30'}, @ActualResult
SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100829')
EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100828'')', {d '2010-08-30'}, @ActualResult
SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100830')
EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100828'')', {d '2010-08-30'}, @ActualResult
END
GO
CREATE PROCEDURE dbo.SQLTest_NearestBusinessDayOnOrBeforeNull
AS BEGIN
DECLARE @ActualResult DATETIME
SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore(NULL)
EXEC TST.Assert.IsNull 'NearestBusinessDayOnOrBefore(NULL) should return NULL', @ActualResult
END
GO
CREATE PROCEDURE dbo.SQLTest_BusinessDaysBetweenEndsOfDaysOutOfRange
AS
BEGIN
DECLARE @ActualResult DATETIME
SELECT @ActualResult = NumDays FROM dbo.BusinessDaysBetweenEndsOfDays('20010827', '20100817')
EXEC TST.Assert.IsNull 'BusinessDaysBetweenEndsOfDays(''20010827'', ''20100817'') should return NULL', @ActualResult
SELECT @ActualResult = NumDays FROM dbo.BusinessDaysBetweenEndsOfDays('20100827', '20990817')
EXEC TST.Assert.IsNull 'BusinessDaysBetweenEndsOfDays(''20100827'', ''20990817'') should return NULL', @ActualResult
SELECT @ActualResult = NumDays FROM dbo.BusinessDaysBetweenEndsOfDays('20010827', '20990817')
EXEC TST.Assert.IsNull 'BusinessDaysBetweenEndsOfDays(''20010827'', ''20990817'') should return NULL', @ActualResult
END
GO