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!
views:
137answers:
3
+2
A:
You can always wrap GetDate() in a custom function and use that everywhere, although it's not an optimal solution.
klausbyskov
2010-04-07 14:03:34
how can you do that? even if you create a UDF it would be `dbo.GETDATE()`
KM
2010-04-07 14:06:08
@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
2010-04-07 14:08:30
KM is correct, it would only then be used by specifying dbo.getdate() , it doesn't override / replace the built in function.
Andrew
2010-04-07 14:10:03
@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
2010-04-07 14:12:26
@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
2010-04-07 14:16:37
@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
2010-04-07 16:29:36
+3
A:
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
2010-04-07 14:04:05
+1
A:
No, there is not much you can do other than something like this:
SELECT GETDATE()-7 --get date time 7 days ago
KM
2010-04-07 14:04:17