SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 GETUTCDATE() in SQL Server 7.0?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/14/2003 :  07:28:52  Show Profile  Visit AskSQLTeam's Homepage
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 - 07/14/2003 :  15:00:43  Show Profile
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37140 Posts

Posted - 07/14/2003 :  15:53:30  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/14/2003 :  16:14:42  Show Profile
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-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37140 Posts

Posted - 07/14/2003 :  16:19:29  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37140 Posts

Posted - 07/14/2003 :  16:34:38  Show Profile  Visit tkizer's Homepage
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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22375

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/14/2003 :  16:42:28  Show Profile
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-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37140 Posts

Posted - 07/14/2003 :  16:46:05  Show Profile  Visit tkizer's Homepage
Well yes that stored procedure would work, but the tough part is knowing whether or not DST is in effect.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/14/2003 :  17:05:20  Show Profile
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-)
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 07/14/2003 :  17:12:36  Show Profile
Damn farmers ...

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

Jonathan
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/15/2003 :  09:25:58  Show Profile
Hey ma..time to feed the chickens and the cows...

(a bovine MOO for Mr. Mist)

MOO



Brett

8-)
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 07/15/2003 :  09:48:27  Show Profile
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}
Go to Top of Page

Henrik Svensson
Starting Member

Sweden
25 Posts

Posted - 07/18/2003 :  06:21:55  Show Profile
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
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 07/18/2003 :  06:33:33  Show Profile  Visit Stoad's Homepage
Henrik Svensson.

Cool!! :)

- Vit
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000