Simulate current date on a SQL Server instance?

Question

Is it possible to change the datetime for a particular database on SQL Server?

Is it tied to the operating system's date/time?

We wish to simulate a future datetime for testing purposes i.e. so the GETDATE() returns a date in the future.

It's got to be in a semi-production (staging) environment so unfortunately changing the OS date / time isn't an option for us.

In an ideal world we'd spin up a virtual server, but also not really an option at the moment.

Answer 1

You can always use this and adjust accordingly:

SELECT getutcdate()

Please see below for more information StackOverflow Question

But there is no way to change the results from a GETDATE() without changing the server's date.

---

Added: You could do a EXEC xp_cmdshell 'DATE 10/10/2011' if you wish... but it's not advised.

Answer 2

I believe you can create a user function that would do the calculation for you and apply that.

http://msdn.microsoft.com/en-us/library/ms186755.aspx

Also, it can be used as the default value for a column.

Bind a column default value to a function in SQL 2005

Answer 3

As stated, by others, No.

A really hacky workaround, would be be to write your own function to return the date you want and have it return GETDATE() when you're done testing, and call that function instead. There's probably some slight overhead in doing this, but it'll do what you need.

Answer 4

Another workaround I've had some success with is to add an INSTEAD OF trigger to any table where a GETDATE() value is being inserted and modify it there e.g.:

ALTER TRIGGER [dbo].[AccountsPayableReceivable_trg_i] ON [dbo].[AccountsPayableReceivable]
INSTEAD OF INSERT
AS
  SET NOCOUNT ON

  SELECT *
  INTO #tmp_ins_AccountsPayableReceivable
  FROM INSERTED

  UPDATE   #tmp_ins_AccountsPayableReceivable 
  SET    dtPaymentMade = '01-Jan-1900'
  WHERE dtPaymentMade between dateadd(ss, -5, getdate()) and dateadd(ss, +5, getdate())

  INSERT INTO AccountsPayableReceivable
  SELECT *
  from #tmp_ins_AccountsPayableReceivable

(Incidentally, the where clause is there because my test script autogenerates these triggers, adding an update for every datetime column, so I only want to update those that look like they are being inserted with a GETDATE() value.)

Answer 5

Unfortunately it is tied to the OS date and time. See here: http://msdn.microsoft.com/en-us/library/ms188383.aspx

This value is derived from the operating system of the computer on which the instance of SQL Server is running.