SQL Server GetDate function to return a future date

Question

This is an existing application that has about ~1400 stored procedures, that was built many years ago. We have used GetDate() to get current date within many of the stored procedures.

Now we want to test this application with "Aging" scenarios. We were told by our DBAs that GetDate() function derives date from operating system. So if we need getdate() to return future date, we need to change OS date. Data Center guys won't let us change Operating System date.

One obvious solution is to create a separate GetApplicationDate() function which returns what ever date we want and review all stored procedures and update them with this new custom function.

I want to avoid changing ~1400 stored procedures and override GetDate() function provided by SQL Server. Is that possible?

Thanks, Gaja

Your DBAs are right. Find someone to override the "Data Center guys" and save you hours of work. However, remember that the OS date is local -- so if you are on the west cost (or india) or where ever, a future time might be as simple as a localization change. — Hogan, Feb 12 '16 at 20:54
NO. GetDate returns the current system datetime. It is native code in sql server so you can't change it. For this type of testing you will have to change the current system time. — Sean Lange, Feb 12 '16 at 20:55
Agree with previous 2 comments. If you don't need to be surgical and can replace EVERY occurance of GETDATE() with your custom function, then it can be quickly done with a copy/replace tool or update to system tables. — Tab Alleman, Feb 12 '16 at 20:57
@JoeStefanelli - In **IT Poker** DBA out-ranks Data Center Guys. — Hogan, Feb 12 '16 at 20:59

score 1 · Answer 1 · edited May 23 '17 at 12:23

There is a similar question here Changing the output of Getdate and it pretty much states that it is not possible to change the value getdate return other than changing the o/s time. Something you could consider is modifyiing all the stored procs by replacing the getdate statements with a wrapper or datetime provider where you can control the value it returns, an offset or the like. This could be controlled by placing the date or offset in a table which the wrapper refers to so that you only need to change it in one place.

Another thing you could consider if this is a large job (due to the number of objects requiring a change) would be to programmatically (automate) make the change to all the objects. There are quite a few hits on google on how to do this.

score 1 · Answer 2 · edited Feb 20 '16 at 12:34

Looks like you have finally reached the point where is no more chance not to refactor your source code. Furthermore, I think the <date> must be an argument of your stored procedures.
Probably you have some reasons to obtain date inside sp's similarly throughout the system, in this case changing reference to a new function looks rational.

To avoid modification of every sp "by hands" you can modify it by regexp script that runs through files on your hard drive and replaces getgate() to a new func name. If you store sql-code in vcs. Or by t-sql script using sys.sql_modules->definition data. Which means you can generate new sp's contents programmatically. But you have to be careful with that.

Also you should keep in mind that

current date may still be required somewhere in your stored procs
altering date returned by new function may cause performance issues
new function has to be deterministic otherwise it can cause additional performance issues

SQL Server GetDate function to return a future date

2 Answers2