Sql-server – Tricking SQL server into thinking it’s a different date

sql serversql server 2014

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.

Best Answer

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)

Related Question