7

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.

loudmummer
  • 544
  • 3
  • 19
Alex KeySmith
  • 16,657
  • 11
  • 74
  • 152
  • Possible duplicate of [Changing the output of Getdate](https://stackoverflow.com/questions/2593047/changing-the-output-of-getdate) – loudmummer Jun 08 '18 at 10:09
  • @loudmummer 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? – Alex KeySmith Jun 08 '18 at 11:35
  • 1
    Indeed, both questions have good answers. It is possible to merge them, but [this](https://meta.stackexchange.com/a/10844) post says only moderators can do it. Perhaps you can flag it for moderator attention? – loudmummer Jun 08 '18 at 11:43

5 Answers5

3

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.

  • 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 :-) – Alex KeySmith Nov 23 '11 at 17:41
  • 1
    @AlexKey No problem, glad to help. There is a `Set-Date` cmdlet for Powershell (http://technet.microsoft.com/en-us/library/ee176960.aspx), but it will alter your OS date and time. –  Nov 23 '11 at 18:37
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.
Community
  • 1
  • 1
SQLMason
  • 3,275
  • 1
  • 30
  • 40
  • 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. – Alex KeySmith Nov 23 '11 at 17:37
  • Sorry, it appears your SQL question is SOL. :) – SQLMason Nov 23 '11 at 17:38
  • 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 :-) – Alex KeySmith Nov 24 '11 at 09:06
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.

Doozer Blake
  • 7,677
  • 2
  • 29
  • 40
  • 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 http://stackoverflow.com/questions/2593047/changing-the-output-of-getdate – Alex KeySmith Nov 23 '11 at 17:45
  • 2
    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. – Ben Thul Nov 23 '11 at 18:06
2

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

David Claughton
  • 79
  • 2
0

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

Community
  • 1
  • 1
CLo
  • 3,650
  • 3
  • 26
  • 44
  • Thanks Chris, I'm trying to avoid changing my code, but similary to Doozer's answer http://stackoverflow.com/q/8246648/141022 it could be an option to switch back and forth. Thanks for your input. – Alex KeySmith Nov 23 '11 at 17:47