Mock Current Date and Time in SQL Server

Occasionally, if you’re especially lucky, you’ll get into a situation where you have SQL procedures and functions that are so complicated that they require unit tests all of their own. They’ll have business logic embedded in them, and not testing them will leave a massive hole in your test coverage.

In this blog post I’m not going to describe how to do that – SSDT are quite well documented anyway. This is about how to deal with dates and times in SQL Server.

A new function

You’ll probably have a few places in your SQL script that call the following:

SELECT GETUTCDATE()

Or, you may even have the following:

SELECT GETDATE()

Which will presumably work well for what you want. Of course, the problem that you have here is, that for unit tests, this presents a variable factor in your test; that is, you’re not always testing the same procedure. Take the following segments of SQL for example:

PROCEDURE MYPROC
AS
BEGIN
	DECLARE @today DATETIME
	DECLARE @hasEntriesAfterToday INT
	
	SET @today = GETUTCDATE()

	SELECT @hasEntriesAfterToday = COUNT(*)
	FROM dbo.MyTable t
	WHERE t.Col1 > @today

	IF (@hasEntriesAfterToday > 0) 	
		select 'test'
END
GO

MyTable contains many entries after today, and my test checks that it returns ‘test’, so the test works, the code works and I’m going to bed.

But what happens in a year’s time?

Let’s say that the last entity in that table is 01/01/2015 (that way the post works in the US, too). As I write this, it is mid-way through June. So, I need to know what will happen on 2nd January 2015. If I do nothing then when it is 2nd January 2015 the test will start to fail, and I won’t know why.

Abstract the date

When faced with this problem, my initial fix was as follows:

CREATE FUNCTION dbo.MyGetDate()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME
	SET @today = GETUTCDATE()

	RETURN @today
END

And then simply change the above procedure to call this. That certainly works; however, as soon as you start to reference this function (for example, you set a default value for a date in a table), you’ll find that you’ll get stuck when you try to mock it out; consequently, you need a double layer:

CREATE FUNCTION dbo.MyGetDate2()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME	
	SET @today = GETUTCDATE()

	RETURN @today
END
GO

CREATE FUNCTION dbo.MyGetDate()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATE	
	SET @today = dbo.MyGetDate2()

	RETURN @today
END
GO

What this then allows you to do is to replace the function of MyGetDate2 without affecting MyGetDate. This is a wrapper function to replace the DateTime:

internal static void OverrideDateTimeTest(SqlConnection cn, SqlTransaction tr, string newDateTime)
{
    string sql =
        "ALTER FUNCTION dbo.MyGetDate2(	" +
        ") RETURNS datetime " +
        "AS " +
        "BEGIN " +
        "DECLARE @value datetime " +
        "SET @value = convert(datetime, '" + newDateTime + "') "  +
        "RETURN @value " +
        "END";
 
    using (SqlCommand cmd = new SqlCommand(sql, cn, tr))
    {
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
}

And here’s the test:

[TestMethod]
public void MyTest()
{
    DBWrapper.OverrideDateTimeTest(cn, tr, "2014-06-10 22:30:00.000");
    Assert.AreEqual( …

The best part about this is that IN SQL SERVER DDL STATEMENTS CAN BE ROLLED BACK! Look at the following test:

-- 1
BEGIN TRAN
GO

-- 2
SELECT dbo.MyGetDate()
GO

-- 3
ALTER FUNCTION [dbo].[MyGetDate2]()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME
	SET @today = GETUTCDATE()

	RETURN @today
END
GO

-- 4
SELECT dbo.MyGetDate()
GO

-- 5
ALTER FUNCTION [dbo].[MyGetDate2]()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME
	SET @today = CONVERT(DATETIME, '2014-06-10 22:30:00.000')

	RETURN @today
END
GO

-- 6
SELECT dbo.MyGetDate()
GO

-- 7
ROLLBACK TRAN
GO

-- 8
SELECT dbo.MyGetDate()
GO

Okay – there’s a fair amount of code, but the stages are as follows (numbered):

1. Start the transaction.
2. Show the existing implementation of MyGetDate2 (in case it’s not what it should be).
3. Change MyGetDate2 to use GetUTCDate(), so it should be the same as before.
4. Check again – should still return the same as 2.
5. Change MgGetDate2 to return hard coded date.
6. Check that it now returns a hard coded date.
7. Rollback the transaction.
8. The transaction is rolled back, and so the function behaves as in 1.

Conclusion

So, we can include a date mock in our test and, should there be a problem, or when we’re finished, it all gets rolled back. Just because I’m always cautious about such things, I’ve created a test that checks that the default implementation returns the current date, but you shouldn’t need this.

3 thoughts on “Mock Current Date and Time in SQL Server

  1. Erik

    Idea is great. However using this mocked function is extremely slowly when used in a WHERE clause instead of the built-in function GETUTCDATE 🙁

    Reply
    1. pcmichaels Post author

      Yeah – DB unit tests are slow in general – if you know of a way to make them any faster then please let me know

      Reply
      1. Erik

        I meant my production code became slow because using the mock, UDF, function is much slower than the built-in GETUTCDATE. I, later, realized I could extract the GETUTCDATE easily out of the code to be tested. e.g. first I had a GetAverageValue which gave me the average for today. Of course, is much better to define a GetAverageValueByDay(day) function that can be unit tested and then make the GetAverageValue a simple wrapper to call GetAverageValueByDay with GETUTCDATE

        Erik

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *