12

Is it possible to deceive SQL Server to return a different date on GetDate() without actually changing the machine date?
This would be great, since we have a database with old data and I'm trying to test some queries that use getdate().
I can change my machine date but that brings some other problems with other applications...
Any tips?
Thanks!

pauloya
  • 2,535
  • 3
  • 30
  • 50
  • The closest I can imagine given the answer below is to create a custom NTP server that feeds the datetime you want. Then, trigger an NTP sync just before your tests. Not perfect, but would suit some needs. – bitsoflogic May 11 '20 at 13:39

5 Answers5

15

According to the documentation for getdate():

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

Since it's derived from the OS, I don't think you can change it separately.

Andomar
  • 232,371
  • 49
  • 380
  • 404
5

You can always wrap GetDate() in a custom function and use that everywhere, although it's not an optimal solution.

Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222
  • how can you do that? even if you create a UDF it would be `dbo.GETDATE()` – KM. Apr 07 '10 at 14:06
  • 1
    @KM: You would change the implementation of the function so you could change it everywhere modifying one place. @Klausbyskov: If I'm not mistaken you can't have a non-deterministic function like GetDate in a UDF. – JoshBerke Apr 07 '10 at 14:08
  • KM is correct, it would only then be used by specifying dbo.getdate() , it doesn't override / replace the built in function. – Andrew Apr 07 '10 at 14:10
  • 2
    @Josh: You cannot call a UDF without a schema prefix, like **dbo**.GetDate. And you can have non-deterministic functions in a UDF: `create function dbo.GetDate() returns datetime as begin return (select GETDATE()) end` compiles fine – Andomar Apr 07 '10 at 14:12
  • @Josh, how do you `change the implementation of the function`? isn't `GETDATE` part of some DLL or EXE inside of SQL Server? and I don't think SQL Server is open source ;-) – KM. Apr 07 '10 at 14:16
  • 1
    @Andomar: I know SQL Server 2000 didn't support calling a non-deterministic function from. Looking in 2008 docs I don't see that listed as a limitation so I guess they changed it. @EveryoneElse: You could write a function called GetMyDate() which you would then update your code to use GetMyDate(), now when you want to hardcode it you can swap the body of GetMyDate() – JoshBerke Apr 07 '10 at 16:29
1

No, there is not much you can do other than something like this:

SELECT GETDATE()-7  --get date time 7 days ago
KM.
  • 101,727
  • 34
  • 178
  • 212
0
SELECT DATEADD(dd, -7, GETDATE())
e_i_pi
  • 4,590
  • 4
  • 27
  • 45
SCOTT
  • 9
  • 1
  • 1
    Well, and how it will help to run many scripts with getdate() without changing them, to test them against data in the past? – Artemix Sep 24 '12 at 10:44
0

One approach is to have an optional fake clock.

Create a single row table (I usually call it dbo.System cos I usually have a number of global parameter values) with a column I call mine CurrentMoment which is datetime2 NULL (so the value can be NULL or a datetime).

Create a function to replace GetDate()

CREATE OR ALTER FUNCTION [dbo].GetDate
    RETURNS datetime2
AS
    BEGIN
        RETURN ISNULL((SELECT CurrentMoment FROM dbo.System), SYSDATETIME());
    END
GO
 -- Yes the above returns a more accurate clock than GETDATE().

Replace ALL references to GETDATE() with dbo.GetDate() - This does require a small change to existing scripts.

With System.CurrentMoment set to NULL all works as normal, real time. But set a value and you have a fake clock, you have to update it as tests/demo proceed.

If you are concerned about performance, you can modify the function so it either returns SYSDATETIME() or the fake datetime as preferred. But I have not found a performance issue worth worrying about.

Alternatively you could have a column in System which is an offset to the current time, and subtract it from SYSDATETIME() when the function is called. That way the value will move on between function calls.

AndyDev
  • 21
  • 2