CODEWITHSUNDEEP
sql-servergetdate
pauloya
pauloya

Reputation: 2563

Changing the output of Getdate

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!

Upvotes: 12

Views: 12537

Answers (5)

AndyDev
AndyDev

Reputation: 31

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.

Upvotes: 0

SCOTT
SCOTT

Reputation: 9

SELECT DATEADD(dd, -7, GETDATE())

Upvotes: 0

KM.
KM.

Reputation: 103667

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

SELECT GETDATE()-7  --get date time 7 days ago

Upvotes: 1

Andomar
Andomar

Reputation: 238246

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.

Upvotes: 15

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121017

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

Upvotes: 5

Related Questions