Tricking SQL server into thinking it's a different date 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.
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)