Showing Date/Times adjusted for remote locations

Question

Showing Date/Times adjusted for remote locations

FRANKNF

SSC Veteran

Points: 285
More actions
March 16, 2003 at 6:15 pm

#81280

I need to generate reports for multiple locations, adjusting the time as held on the server to apply for remote timezones(inc daylight saving adjustments).
I know what the offset for each location is to GMT but Daylight saving adjustments are beyond me at the moment
Is there any easy way to access Windows time for other locations via sql .
Or any other suggestions?
I am using SQL 7.
Franknf

Franknf

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Assuming US, Daylight Savings Time adjustments are based on the first Sunday of every April and last Sunday in October at 2AM. We hand calc ours and store the base value as GMT to make this easier. As for getting Windows time difference the answer is not in SQL but in other programming languages you can.

Here is one of the scripts I put together for GMT conversion. Of course all my sites are Eastern I deal with but I deal with data froma server that is not. You will need to tweak to your needs. http://www.sqlservercentral.com/scripts/contributions/158.asp

guarddata SSCertifiable Points: 6583 More actions · Answer 2

SQL 7 had an ugly way to get GMT. Something like this...

(courtesy of Microsoft PSS) :-

declare @deltaGMT int

exec master.dbo.xp_regread

'HKEY_LOCAL_MACHINE',

'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

'ActiveTimeBias',

@DeltaGMT OUT

select getdate() as LocalTime, dateadd(minute, @deltaGMT, getdate() ) as GMT

In addition to the routines Antares talks about, you can do it within your stored procedure:

Convert Local to UTC

select DATEADD( MINUTE, DATEDIFF( MINUTE, GETDATE(), GetUTCDate()), <Local Time>)

Convert UTC to Local

select DATEADD( MINUTE, DATEDIFF( MINUTE, GetUTCDate(), GETDATE()), <UTC Time>)

Guarddata-

FRANKNF SSC Veteran Points: 285 More actions · Answer 3

Thanks Antares for the sample proc & Guarddata for the clue bout getting GMT.

I'll have to tweak the proc using the clue to satisfy my users, who want to use base data and actually adjust times to localtimes,including historic info.

Franknf