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.

Valeryvalerye

Possible duplicate of Changing the output of Getdate – Wallet

@Wallet I think you're correct they appear to be the same question, I didn't come across that question at the time. Both questions (very old) have attracted valuable insights, is it possible to merge them? – Valeryvalerye

Indeed, both questions have good answers. It is possible to merge them, but this post says only moderators can do it. Perhaps you can flag it for moderator attention? – Wallet

Answer 1

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.

Calyptra

Interesting point (+1 from me), got me thinking if there is a way of overriding GETDATE()... looks like I might be able to using the full syntax dbo.GETDATE() it does mean changing my code... but wouldn't be too bad, however I'm running on a 2005 DB under 2000 compatibilty mode so I'm probably asking for trouble regarding deterministic / non-deterministic functions etc #2593547 – Valeryvalerye

For the love of all that is holy, please don't call your new function getdate. I can't think of an easier way to generate confusion than co-opting the name of a built-in function for your own purposes. If you're already changing your codebase to accommodate for your new function, make it obvious that it's doing something else. Hell, MYGETDATE would be better. – Swayback

Answer 2

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.

Inchon

Thanks Shark, +1 from me. I was hoping for some sort of magical workaround... :-) I wonder if there's any hardcode powershell script for altering date times for a specific exe / thread - though that sounds near impossible / v dangerous! Looks like I'll have to look further into the virtual server option..... time for EC2 I think :-) – Valeryvalerye

@AlexKey No problem, glad to help. There is a Set-Date cmdlet for Powershell (technet.microsoft.com/en-us/library/ee176960.aspx), but it will alter your OS date and time. – Inchon

Answer 3

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.

Cal

Hi, thanks for the response, but unfortunately it's not for adjusting a particular call to GETDATE(), but instead all calls to GETDATE() so we can test existing code. To make the tests more robust we're trying not to change the code, but instead tweaking the environment. Thanks anyway. – Valeryvalerye

Sorry, it appears your SQL question is SOL. :) – Cal

Interesting point on xp_cmdshell thanks. I don't think it's enabled on our staging environment, but I'll keep the cmd line in mind, thanks. +1 from me :-) – Valeryvalerye

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.)

Evangelize

Great idea, I'm in a different world now so won't get around to using it, but I like the theory! +1 from me. – Valeryvalerye

Answer 5

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

Afoul

Thanks Chris, I'm trying to avoid changing my code, but similary to Doozer's answer https://mcmap.net/q/1459196/-simulate-current-date-on-a-sql-server-instance/141022 it could be an option to switch back and forth. Thanks for your input. – Valeryvalerye