GETUTCDATE() in SQL Server 7.0? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Old Forums

CLOSED - General SQL Server

GETUTCDATE() in SQL Server 7.0?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-14 : 07:28:52

Henrik writes "Hi!

In a stored procedure I would like to store both GETDATE() and GETUTCDATE() to a table containing the columns LastModifiedDateTime and LastModifiedUTCDateTime. The only problem is that GETUTCDATE() doesn't exist in SQL Server 7.0 (only in SQL Server 2000).

Is this possible to accomplish in T-SQL for SQL Server 7.0 in some way?

[@@Version = Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)]

with best regards,
Henrik Svensson"

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 15:00:43

Well, you'd have to worry about daylight savings time, from where every you are and if they have that concept in Greenwhich.

I thought we 5 hours behind, but I guess because of DST, it's now 4.

Anyway, simply:

SELECT GETUTCDATE() As Geenwich
, GETDATE() AS NJ
~~, GETDATE() + 4 AS Derived Greenwich~~
, DATEADD(hh,4,GETDATE()) AS DerivedGreenwich

[homer]dooooooooooooooooooooooooooh[/homer]

Brett

8-)

Edited by - x002548 on 07/14/2003 15:23:45

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-14 : 15:53:30

Why would you want to store both values in a table? As long as you have one value, you can get the other by doing the conversion.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 16:14:42

Or you can create a udf


USE Northwind
GO

CREATE FUNCTION udf_GetUTCDate(@x datetime, @DTS char(1))
Returns Datetime
AS
BEGIN
	DECLARE @y int
	IF @DTS = 'Y' SELECT @y = 4 ELSE SELECT @y = 5
	Return DATEADD(hh,@y,@x)
END
GO

SELECT GetDate(), dbo.udf_GetUTCDate(GetDate(),'Y')
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-14 : 16:19:29

Henrik has SQL Server 7.0 which does not support UDFs and does not support GETUTCDATE(). He will need to perform the conversion in a stored procedure.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-14 : 16:34:38

To answer Henrik's question though, yes you can do this in SQL Server 7.0, but you will need to convert the time to GMT. To do the conversion, you need to know if the source timezone supports DST. If it doesn, then you need to account for that. You also need to know when DST begins and end. It also helps to have a table that contains timezone information. I wrote a UDF that converts a give date and time to the specified timezone. It can easily be rewritten so that it is a stored procedure and not a UDF. You can find this UDF here:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22375[/url]

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 16:42:28

Alrighty then...



USE NMorthwind
GO

CREATE PROC usp_GetUTCDate @x datetime, @DST char(1), @z datetime OUTPUT
AS
BEGIN
	DECLARE @y int
	IF @DST = 'Y' SELECT @y = 4 ELSE SELECT @y = 5
	SELECT @z = DATEADD(hh,@y,@x)
END
GO

DECLARE @z2 datetime, @x datetime
SELECT @x = GetDate()
EXEC usp_GetUTCDate @x, 'Y', @z = @z2 OUTPUT
SELECT @z2
GO

DROP PROC usp_GetUTCDate
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-14 : 16:46:05

Well yes that stored procedure would work, but the tough part is knowing whether or not DST is in effect.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 17:05:20

Yeah,

nad that's why I took the easy way out...hey s/he's gotta do some research too...

Maybe they'll post what the find out.

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-14 : 17:12:36

Damn farmers ...

Daylight savings time is the biggest sham ever perpetrated. It's even optional!! Time itself is optional!!

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-15 : 09:25:58

Hey ma..time to feed the chickens and the cows...

(a bovine MOO for Mr. Mist)

MOO

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-15 : 09:48:27

Yes but come summertime it's still light out at the same time it used to still be dark ... roll the clocks back!!

Jonathan
{0}

Henrik Svensson
Starting Member

25 Posts

Posted - 2003-07-18 : 06:21:55

At the local server I use the server-time (and so does already running applications, which I don't intend to adjust), but at the subscribing system (where I replicate the table) the data need to be converted to UTC. And since the database could be used by different customers from different parts of the world, the procedure has to be non-specific.

But I found a solution that seems to work:

DECLARE @ActiveTimeBiasInMinutes INT
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias',
@ActiveTimeBiasInMinutes OUTPUT

LastModifiedUtcDateTime = DATEADD(MINUTE,@ActiveTimeBiasInMinutes, @LastModifiedDateTime)

with best regards,
Henrik Svensson

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-18 : 06:33:33

Henrik Svensson.

Cool!! :)

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources