Best practice to unit test stored procedure that uses getdate()

Question

I have a stored procedure that uses getdate() for taking a current date and compare with provided.

But a problem with testing arose -> usually for testing we have prepared input and output data, and predefined dates among this data. But getdate() will always return current time, so test results will differ in time.

I think there is no way to change what getdate() will return? But take getdate() out of the stored procedure and use some input parameter instead also is not an option.

What are the best practices to test such stored procedure, to make the test's result constant?

Answer 1

Looks like it can't be done short of changing the server date or modifying the stored procedure:

Changing the output of Getdate

Under these constraints, you can check if the procedure returns a result set without errors. If the procedure queries other tables, you could populate those tables with data based on the current date, call the procedure, validate the results, then remove the data from the other tables.

Alternately, if the procedure is truly fixed and unchanging, the cost of automated testing may be higher than the value of it in this (rare) case. You could validate the procedure manually (QA).