Tricking SQL server into thinking it's a different date

Question

I have a unique problem (at least for me) that I have solved in one way but would like an easier way if possible :)

I often have to test our Windows app with dates in the past. Our app queries a SQL server which uses GETDATE.

I have setup a VM with our app and SQL server on it. I can change the system date backward or forward as needed after doing the below through powershell:

• stopping windows time service
• if running on HyperV, stop the vmictimesync service
• setting the new date
• restart time services
• restarting the sql server (just in case) and our app

It seems to work very well - both our app and the SQL server can't tell the difference.

If I need to reset the date back to current I simply restart the windows time service. Easy.

However, it's a bit of effort to set that up at moments notice and was wondering if there is a way to fool the SQL server or an individual database into thinking it's a different date as needed?

So with the individual db perhaps I can set some properties such that it would shift GETDATE calls back to a certain time period.

All a stretch I know but if anyone has any other ideas I'm all ears.

Unfortunately our devs can't change the app to make this all easier.

Answer 1

The short answer: no. Ask the developers to fix the problem.

The long answer: it is possible to detour system calls. See for example Modifying application behavior with Detours (for Application Compatibility reasons). This allows you to modify the result of a system call for a specific process. In the linked example it is the GetSystemTime call and the author modifies it to return a date in the past. You can do similar and load sqlservr.exe in a detour environment and return the date of your choice.

However, I would not call this method 'easier' than modifying the system clock. And for sure is riskier. Not to mention, I'm not even sure the GETDATE translates to a call to GetSystemTime...

---

You could also try RunAsDate

RunAsDate is a small utility that allows you to run a program in the date and time that you specify. This utility doesn't change the current system date and time of your computer, but it only injects the date/time that you specify into the desired application.

Look at your service parameters (eg. sc.exe qc MSSQLSERVER). You must match the startup parameters (what sc.exe reports as BINARY_PATH_NAME) and the startup user (ie. SERVICE_START_NAME). If you don't run as the correct user you simply won't have access to the required files (master, tempdb, logs etc).

See How to: Start an Instance of SQL Server (sqlservr.exe)

Answer 2

You could replace all instances of GETDATE (and equivalents) with your own function (e.g. ACMEGETDATE if your company's name is ACME), and have this function return either GETDATE straight, or when detecting certain circumstances (like servername starting with 'test'), DATEADD(GETDATE(), ...) from a global table or something.

This will cause an impact in production, so you have to weight how much this solution would cut developing and testing costs (and improve the product's output) against any possibly noticeable performance degradation and other risks (performance, compatibility, etc).

Answer 3

I've had the same issue - running automated tests that require different actions on different days.

As mentioned above, the best solution is to replace application calls that get the datetime, and sql calls to GETDATE() with a custom function that can return a configured date.

That was unfeasible in my situtation, so we incorporated changing the machine date/time in the test scripts.

We had to...

1. Stop the windows time service - net.exe stop "Windows Time". If you don't, it will restore the correct time.
2. Change the date (we used the Windows API through .Net)
3. wait in a loop until the SQL GetDate() function returned the correct date. There seemed to be a delay when sql picked up the new time.

This was all done on a local database instance, so scheduled tasks was not a problem.