datetime in the zone - 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 2000 Forums

SQL Server Development (2000)

datetime in the zone

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 13:24:19

Paul writes "Okay, I think this isn't possible, but I'll let the Guru determine that.

I want to have a stored procedure that returns the current datetime that is time zone dependent.

Inputs I am willing to offer are:
1. Number of hours between destination zone & GMT(UTC)
2. Whether or not they use DST ( daylight savings time )
3. A datetime in EST( DST Observed )

Can get from the system:
The current time in EST ( DST Observed ) GetDate()
The current time in UTC GetUTCDate()

Output:
The datetime in another timezone

This seems to be a fairly nasty problem. I have a lot of experience and do not see a way to do this.

Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

-- Paul

PS. I want the T-Shirt!"

horked_noodle
Starting Member

2 Posts

Posted - 2002-02-19 : 14:10:39

quote:

Two questions:

First, would this be easier to do outside of SQL Server? Can you find some already written code in VB or C or some such?

Second, what t-shirt?

First, I do really want to do it in the database. This code will be shared amongst several applications and I want to minimize the chance that it will get ... political crap omitted ... I want a SQL Server Solution.

Second, the Web page that got me here said that a T-Shirt was the prize for stumping the SQL-Guru.

Thanks,

--Paul

horked_noodle
Starting Member

2 Posts

Posted - 2002-02-19 : 14:12:55

I don't think it would be any easier outside of SQL Server, I want the code to be centralized because it's shared amongst several apps.

According to the page that sent me here a T-Shirt was the prize for stumping the SQL Guru.

Thanks,

--Paul

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-02-19 : 14:35:22

Just wondering whether or not a ActiveX Script in a DTS package would be a possibility?

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-19 : 16:07:27

The basis of this is really simple:

CREATE PROCEDURE GetLocalDatetime
@NumberOfHours INT,
@DST TINYINT
AS

SELECT DATEADD(HH, @DST + @NumberOfHours, GetUTCDate())
GO
EXEC GetLocalDatetime -5,0

I realize that this might be over simplified from what you expect, but whatever you want would not be that hard to get around. Maybe add a location parameter and build a table or to to control when DST is on and when it is off for a given location.

All of that is of course reinventing the wheel. How about a web call with and extended sp to a site like http://www.worldtimeserver.com/index.asp
as an option.

BTW this is just the first site I found that might work as opposed to a recommendation.

Edited by - toddv on 02/19/2002 16:32:43

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-19 : 16:25:32

Paul,

I would also suggest doing this outside of SQL Server. This is a "Non-Database" process, and would be consuming cycles on your database server.

I don't know if the site that Todd suggests offers an XML web service, but this would be a perfect candidate for an XML Web Service.

HTH
-Chad

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-02-19 : 16:37:07

Another option is to set up a table with the 24 Time Zones and a difference qualifier (i.e. CST -6). Then the following SP:

Create Procedure get_time_zone
@Remote_Time datetime,
@GMT datetime
AS
DECLARE @Time_Zone varchar(50)

Select @Time_Zone = Time_Zone_Name
From TIME_ZONES
Where Time_Zone_Diff = datediff(hh,@Remote_Time, @GMT)

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-19 : 17:39:38

quote:
Second, the Web page that got me here said that a T-Shirt was the prize for stumping the SQL-Guru.

Just out of curiousity ... you don't happen to have that web site handy can could post it do you?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-19 : 18:49:43

graz-

It's on the 4Guys Stump the SQL Guru page

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-19 : 23:31:37

Here's something that seems to work -- I'll offer this up to the rest of you yaks for some refinement. This uses the local system's registry and takes the GMTOffset as an optional input paramter (if you don't provide an input parameter it just returns GMT). It returns the time for the given timezone as an output.

/*This procedure accepts GMT offset as an input parameter
and returns the current time for that timezone */

CREATE PROCEDURE sp_GetTime
@GMTOffset DECIMAL(3,2) = NULL,
@TheTime DATETIME = NULL OUTPUT
AS
DECLARE @GMTOffsetINT INT
DECLARE @DeltaLocalTime DATETIME
DECLARE @DeltaGMT INT
SET @GMTOffsetINT = ROUND(@GMTOffset*60,0,1)
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias',
@DeltaGMT OUTPUT
SET @DeltaLocalTime = DATEADD(MINUTE, @GMTOffsetINT, GETDATE())
SET @TheTime = DATEADD(MINUTE, @DeltaGMT, @DeltaLocalTime)
SELECT @TheTime AS TheTime

/*
I modified this so it will now accept a decimal value as an input parameters for timezones that are non-integers (e.g., 6.25).
*/

Edited by - SPYDER on 02/23/2002 03:58:48

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-22 : 01:43:19

Just curious -- anybody have any feedback on the code I submitted -- I'm curious if this meets the criteria that the original poster was wanting. Thanks!

Doug G
Constraint Violating Yak Guru

331 Posts

Posted - 2002-02-22 : 19:11:57

Passing just an hours offset won't work for all time zones, like Afganistan and Newfoundland where the offset includs a 1/2 hour difference.

That said, I have no answer to the problem.

======
Doug G
======

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-23 : 03:57:26

quote:

Passing just an hours offset won't work for all time zones, like Afganistan and Newfoundland where the offset includs a 1/2 hour difference.

Doug, take a look at it now -- this should do the trick.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-23 : 10:36:17

quote:

graz-

It's on the 4Guys Stump the SQL Guru page

That was the first place I looked (http://www.4guysfromrolla.com/webtech/sqlguru/). I didn't see a t-shirt mentioned. There was one a looong time ago though. I'm just trying to make sure I'm not promising more than I can deliver :)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources