Using tSQLt, how can I isolate the dependency on the system clock when using GETUTCDATE()?

Question

I have a stored procedure that uses the GETUTCDATE() function several times. It's a very specific line of business sproc, so it probably wouldn't make a lot of sense to show it here. Having said that, it might be helpful to know the sproc will only ever be called for the current year. Here's a contrived example that doesn't show the complexity of what I'm doing, but should help illustrate what I'm talking about:

CREATE PROCEDURE dbo.GenerateRequestListForCurrentYear AS BEGIN
  SELECT RequestId, StartDate, EndDate FROM Requests
  WHERE YEAR(EndDate) = YEAR(GETUTCDATE());
END;

My test looks like this:

CREATE PROCEDURE testClass.[test Requests are generated for the current year] AS BEGIN
  -- arrange
  EXEC tSQLt.FakeTable 'dbo.Requests';
  INSERT INTO dbo.Requests (RequestId, StartDate, EndDate) VALUES
    (1, '2/1/14', '2/10/14'), (2, '2/1/13', '2/10/13');

  SELECT TOP (0) * INTO #Expected FROM dbo.Requests;
  SELECT TOP (0) * INTO #Actual FROM dbo.Requests;
  INSERT INTO #Expected VALUES
    (1, '2/1/14', '2/10/14');

  -- act
  INSERT INTO #Actual
  EXEC dbo.GenerateRequestListForCurrentYear;

  -- assert
  EXEC tSQLt.AssertEqualsTable #Expected, #Actual;
END;

I see a couple options:

1. Pass in the date/time as a parameter (and set the value to GETUTCDATE() if NULL)
2. Replace the call to GETUTCDATE() with my own function to return the date/time that I can fake using tSQLt
3. ?

Both of these seem like options that are only necessary for the test which seems a little smelly; there wouldn't ever be a need to parameterize this sproc with regard to how it's being called from the primary application.

As it relates to dependencies with built-in functions in SQL that have their own dependencies, is there a way to fake those calls in a tSQLt test? Is there a better way to fake the call to GETUTCDATE() to return a date I specify in my tests using tSQLt or are these my only options?

Halftimbered

I would go with 2 but if you want something really smelly change the system time before the call. Actually if you are only going to test a few times it is not that smelly and requires no change to you procedure. – Khalsa

Answer 1

You can use either approach, and like Andrew, I have used both in the past.

I generally prefer option 1 (passing in the parameter) whenever I can however. It has the added benefit of making an execution more repeatable. In other words, I can pass in an arbitrary date and that is often useful for support and has on more than one occasion made adding a new feature easier. I find this useful in languages outside of SQL also. It has the effect of making the result of the individual, complex piece of code not dependent on when it was executed, but instead on the parameters received and the data in the system. In other words, there is a benefit beyond testing of passing in the current time as a parameter and may ease your hesitation. (It should be noted that I also believe that there is nothing wrong with making a design decision to support testing - it is an important part of the product).

Option 2 also works, and you would just simply create your own function that wraps the system function. You should be aware however, that this may have implications on performance and you should test it thoroughly. SQL Server is not always kind to you when functions are involved in queries. I don't like the advice to never use scalar functions in queries due to supposed performance problems, because sometimes there is not a performance penalty and sometimes the difference in performance is a non-issue. However, you should be aware of the potential if you decide to wrap the function.

Illiberal

Answer 2

I would generate my test data in the test, such that the test always had reliable data for the current year (i.e. use relative dates). That way the data will always return the expected result.

Of course, the call to GETUTCDATE() is a dependency, but in truth you would find it difficult (or impossible) to isolate from most system functions; I think it is more important to test the date functionality which will be used.

To use your example:

CREATE PROCEDURE testClass.[test Requests are generated for the current year] AS BEGIN
  -- arrange
  EXEC tSQLt.FakeTable 'dbo.Requests';
  INSERT INTO dbo.Requests (RequestId, StartDate, EndDate) --Calculate working example in current year
    SELECT 1,'2/1/'+convert(nvarchar(4),year(GETUTCDATE())),'2/10/'+convert(nvarchar(4),year(GETUTCDATE()))
  INSERT INTO dbo.Requests (RequestId, StartDate, EndDate) --Calculate previous year example
    SELECT 2,'2/1/'+convert(nvarchar(4),year(dateadd(year,-1,GETUTCDATE()))),'2/10/'+convert(nvarchar(4),year(dateadd(year,-1,GETUTCDATE())))

  SELECT TOP (0) * INTO #Expected FROM dbo.Requests;
  SELECT TOP (0) * INTO #Actual FROM dbo.Requests;
  INSERT INTO #Expected 
    SELECT 1,'2/1/'+convert(nvarchar(4),year(GETUTCDATE())),'2/10/'+convert(nvarchar(4),year(GETUTCDATE()));
  -- act
  INSERT INTO #Actual
  EXEC dbo.GenerateRequestListForCurrentYear;

  -- assert
  EXEC tSQLt.AssertEqualsTable #Expected, #Actual;
END;

I prefer this approach, as although it is a little more work to code the test, and requires thinking about the expectation of the test in future time periods, it allows you to code with no interference to the code under test, and allows you to know the test will still function in future years.

Preferable

Thanks for the alternative, @DaveGreen. I hadn't thought about it from that perspective as I typically think of my expectations as static. – Halftimbered

@Preferable this is a possible solution, but even though GETDATE() and GETUTCDATE() are non-deterministic i.e. they return the same value within the scope of a query, in a stored procedure I would assign GETDATEUTC() to a variable. I guess in your example where you are only considering the date you would only have a very small chance of the test failing if it runs as the year changes. – Arcade

@Arcade - I take your point about the value changing during the test assemble/act sections, and if looking at smaller intervals (e.g. time) then I'd set the data then extract based on the specified ID. The time period for the returned value to change is small (when looking at longer dates - in this example a year) and the risk of it being called at that time negligible for my purposes, but I do agree with your point that it could cause a false failure for some small intervals of time, and would be more difficult to troubleshoot. Thanks for pointing it out. (+1) – Preferable

That might lead to tests, that only fail on special dates, e.g. change of daylight saving time etc. – Ludlow

Answer 3

I have used both options 1 and 2 above. In your particular example I would prefer option 1 where you have a default parameter unless you are working on a greenfields project where you can define functions to return datetime and datetimeutc upfront and then define their usage as the standard going forward. I personally think the default parameter option is just a little easier and clearer.

Arcade

Answer 4

I've tried both approaches with my system and found that implementing my own wrapper function was the better solution. Passing in the date as a parameter is appealing if you're only dealing with stored procedures but, as I found out the hard way, it can be problematic for views that perform calculations around the date. I'd suggest making a GETUTCDATE helper function and using that instead of calling the system function directly. You'll end up with more control at the end of the day and that enables you to create more/better tests.

Katlaps