Simulate current date on SQL Server instance?

Question

Can I change the date and time for a specific database on SQL Server?

Is it related to the date / time of the operating system?

We want to model the future datetime for testing purposes, i.e. GETDATE() returns a date in the future.

It should be in a semi-production (intermediate) environment, so, unfortunately, changing the date / time of the OS is not an option for us.

In an ideal world, we deploy a virtual server, but at the moment this is also not an option.

Answer 1

As indicated by others, No.

The real hacker solution is to write your own function to return the desired date and return it GETDATE () when you finish testing, and call this function. There is probably some small overhead in doing this, but it will do what you need.

Answer 2

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

This value is derived from the operating system of the computer that starts the instance of SQL Server.

Answer 3

You can always use this and adjust accordingly:

 SELECT getutcdate() 

See below for more details. fooobar.com/questions/46088 / ...

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

---

Added: You can do EXEC xp_cmdshell 'DATE 10/10/2011' if you want ... but this is not recommended.

Answer 4

Another workaround with which I was successful is to add an INSTEAD OF trigger to any table that has the GETDATE () value inserted, and change it there, for example.

 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 

(By the way, the where clause exists because my test script automatically generates these triggers, adding an update for each datetime column, so I only want to update those that look as if they were inserted using the GETDATE () value.)

Answer 5

I believe that you can create a custom function that will do the calculations for you and apply this.

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

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

Bind default column value to function in SQL 2005