Unit test use datefield with preset GETDATE

Related products: Software Factory
  • 30 April 2021

The Software Factory offers a solution in which you can configure unit tests for various functionalities/procedures. You are capable of comparing an expected output of your procedure with an actual output. This is a great way to verify whether changes within your current version might have broken this procedure/functionality.

However several procedures use the GETDATE() option. This will lead to the fact that the actual output will never by equal to the expected output, since this was using a different date (and or time) stamp. 

I would like to be able to overrule the GETDATE() statement within my code into a preset date/time. When this is possible I will be able to compare the expected an actual output properly for these kind of procedures/functionalities. 

Isn't this already kind of possible by replacing the all the date functions with testable custom functions? This function could return depending on for example the database name or another property a constant date value.

In addition you could create a custom validation that searches for all regular date functions that should not be used.

create function app_getdate()
returns datetime2

if @@servername = 'app-unit-test'
-- or db_name() = 'test'
-- or dbo.tsf_user() = 'unit-test-runner'
-- or if exists (select 1 from property p where p.key = 'unit-test' and p.value = 'true')

return cast('2000-01-01 00:00:00' as datetime2)


return getdate()



select dbo.app_getdate()


That is not what I am looking and setting up a function like this to replace GETDATE will decrease the performance of your system. A Database function is always slower then a SQL function such as GETDATE().

Release 2021.2 will allow you to add a preparation query which can be used to modify test input parameter values. This will allow you to use parameters that use the current date and time.

Furthermore, any unit tests will be able to use an assertion query instead of just tasks and triggers. The unit tests output will be available as variables. This will allow you to perform assertions on the output using the current date and time as well.

Will this resolve the problem without having to rely on mocked system functions?