GETUTCDATE() and time travel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

GETUTCDATE() and time travel

Author

Topic

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-30 : 11:24:20

I have a surprise requirement in a project for my db to support time travel when deployed in specific environments for QA purposes. One of the ideas that we have as a team is to overload the GETUTCDATE() function on the environment to pull a preset date stored in a table rather than the current system time. My problem though is that I can't seem to find where GETUTCDATE() is defined to validate that I can actually alter its definition. Anyone have an idea of where I can find it?

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-30 : 11:36:35

It's a built-in system function for SQL Server, you can't modify it. You might be able to create a user-defined function based off of it, but it may not work since it's a non-deterministic function.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-30 : 11:40:39

That's what I was afraid of. Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-30 : 12:06:59

quote:
Originally posted by jdaman

I have a surprise requirement in a project for my db to support time travel...

"time travel"? What exactly does that mean?

CODO ERGO SUM

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-30 : 12:10:48

Time travel?! And my company just upgraded MS Office to 2003! Man, we're further behind the tech curve than I thought.

Jim

Everyday I learn something that somebody else already knew

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-30 : 13:11:27

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by jdaman

I have a surprise requirement in a project for my db to support time travel...

"time travel"? What exactly does that mean?

CODO ERGO SUM

It means running the entire project forward in time so that QA can test and sign off on processes that are time based.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-30 : 13:19:45

I got this to work:

CREATE TABLE myDate(myDate DATETIME NOT NULL)
INSERT myDate VALUES(DATEADD(DAY, 50, GETUTCDATE()))
GO

CREATE FUNCTION dbo.newdate(@usetabledate BIT=0) RETURNS DATETIME AS
BEGIN
	RETURN (SELECT CASE WHEN @usetabledate=1 THEN myDate ELSE GETUTCDATE() END FROM myDate)
END
GO

SELECT dbo.newdate(1)
SELECT dbo.newdate(0)

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-30 : 13:26:10

quote:

Originally posted by robvolk

I got this to work:

CREATE TABLE myDate(myDate DATETIME NOT NULL)
INSERT myDate VALUES(DATEADD(DAY, 50, GETUTCDATE()))
GO

CREATE FUNCTION dbo.newdate(@usetabledate BIT=0) RETURNS DATETIME AS
BEGIN
	RETURN (SELECT CASE WHEN @usetabledate=1 THEN myDate ELSE GETUTCDATE() END FROM myDate)
END
GO

SELECT dbo.newdate(1)
SELECT dbo.newdate(0)

Yeah, this would work but it would mean that I would either have to have 2 separate code branches, one for QA and another for production, or I would have that udf active in my production environment which I would rather avoid. I may try writing a QA post deployment script that will go in and replace GETUTCDATE() with a udf in all procedures, functions, and default constraints. It would be ugly but it would keep the code out of my production environment. Depending on the effort I may have to just go with your suggestion.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-30 : 13:34:59

Here's a tweak that might help:

CREATE FUNCTION dbo.newdate(@usetabledate BIT=0) RETURNS DATETIME AS
BEGIN
	RETURN (SELECT CASE 
		WHEN @usetabledate=1 AND @@SERVERNAME IN('QA_SERVER1','QA_SERVER2')
		THEN myDate 
		ELSE GETUTCDATE() END 
	FROM myDate)
END
GO

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-30 : 13:48:09

quote:

Originally posted by robvolk

Here's a tweak that might help:

CREATE FUNCTION dbo.newdate(@usetabledate BIT=0) RETURNS DATETIME AS
BEGIN
	RETURN (SELECT CASE 
		WHEN @usetabledate=1 AND @@SERVERNAME IN('QA_SERVER1','QA_SERVER2')
		THEN myDate 
		ELSE GETUTCDATE() END 
	FROM myDate)
END
GO

Unfortunately environment separation is at the domain level and all use the same servername for the db.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-30 : 13:55:27

You're just doing this because you like the attention:

ALTER FUNCTION dbo.newdate(@usetabledate BIT=0) RETURNS DATETIME AS
BEGIN
	RETURN (SELECT CASE 
		WHEN @usetabledate=1 AND DEFAULT_DOMAIN() IN('QADOMAIN1','QADOMAIN2')
		THEN myDate 
		ELSE GETUTCDATE() END 
	FROM myDate)
END
GO

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2011-03-30 : 14:10:28

quote:

Originally posted by robvolk

You're just doing this because you like the attention:

ALTER FUNCTION dbo.newdate(@usetabledate BIT=0) RETURNS DATETIME AS
BEGIN
	RETURN (SELECT CASE 
		WHEN @usetabledate=1 AND DEFAULT_DOMAIN() IN('QADOMAIN1','QADOMAIN2')
		THEN myDate 
		ELSE GETUTCDATE() END 
	FROM myDate)
END
GO

This is a good compromise and may work. I need to make sure that I am correct about where our separation is at. In our lower environments we all use the same domain but production should be on a separate domain I would hope. I would in the least be able to ensure that we would never have the code return anything except current system time in production which is the biggest concern.

Thanks for staying after it with me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources