October 27, 2011 at 6:35 pm
Hi,
I have to allow users enter dates that are future or past dates based off the offset values that are stored in the database. So if today is 10/27/2011 and the offset value for days is +3 when sql sever calls getdate() it should return 10/30/2011. I know/think that sql sever date comes from the operating system that it is installed,but I can't change the date for the operating system because other applications will be affected. Is there a way to put a wrapper around the core getdate() so that it returns the date based off the offset value? or has anyone done anything like this?
Thanks
October 27, 2011 at 6:44 pm
bdotjones (10/27/2011)
Hi,I have to allow users enter dates that are future or past dates based off the offset values that are stored in the database. So if today is 10/27/2011 and the offset value for days is +3 when sql sever calls getdate() it should return 10/30/2011. I know/think that sql sever date comes from the operating system that it is installed,but I can't change the date for the operating system because other applications will be affected. Is there a way to put a wrapper around the core getdate() so that it returns the date based off the offset value? or has anyone done anything like this?
Thanks
Not with GETDATE(), no.
You could generate a CLR function, or even just a database function, to replace this. However, you can't directly overload the GETDATE() system function. You're correct, however, it reads off the system clock.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2011 at 7:15 pm
Evil Kraig F (10/27/2011)
Not with GETDATE(), no.
You could generate a CLR function, or even just a database function, to replace this. However, you can't directly overload the GETDATE() system function. You're correct, however, it reads off the system clock.
Is there a way to hook into the event when getdate() is called and then modify the value from the system clock and pass back the new value?
October 28, 2011 at 12:56 pm
bdotjones (10/27/2011)
Evil Kraig F (10/27/2011)
Not with GETDATE(), no.
You could generate a CLR function, or even just a database function, to replace this. However, you can't directly overload the GETDATE() system function. You're correct, however, it reads off the system clock.
Is there a way to hook into the event when getdate() is called and then modify the value from the system clock and pass back the new value?
No method I'm familiar with. I've never heard of it being done and I believe it's too deep in the parser to adjust. If you find a way, we'll all be curious. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 30, 2011 at 12:33 pm
What's wrong with DATEADD(dd,3,GETDATE())???
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2011 at 12:54 pm
bdotjones (10/27/2011)
Is there a way to hook into the event when getdate() is called and then modify the value from the system clock and pass back the new value?
Without rewriting portions of SQL Server, no.
Why don't you either let the users enter the date from the front end and store that, or enter an offset that you calculate in the stored procedure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 7:28 am
Jeff Moden (10/30/2011)
What's wrong with DATEADD(dd,3,GETDATE())???
GilaMonster (10/30/2011)
bdotjones (10/27/2011)
Is there a way to hook into the event when getdate() is called and then modify the value from the system clock and pass back the new value?Why don't you either let the users enter the date from the front end and store that, or enter an offset that you calculate in the stored procedure?
@Jeff- dataeadd is fine but it will require a manual change in all of our stored procedures which will be a huge overhead
@GilaMonster - The users are going to enter the offset and then its stored in a table. At the beginning of each process we always get the current date,so when that's done I will then use that offset value to get the offset current date. Then for all date saved in the database i will have a trigger to recalculate the date based of the offset if it exists for that user
October 31, 2011 at 8:50 am
bdotjones (10/31/2011)
@GilaMonster - The users are going to enter the offset and then its stored in a table. At the beginning of each process we always get the current date,so when that's done I will then use that offset value to get the offset current date.
So then you're going to have to modify the process where it gets the current date to get the current date with offset computed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply