Sql server Time zone problem - 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

Other SQL Server 2008 Topics

Sql server Time zone problem

Author

Topic

sudhakar_kota
Starting Member

1 Post

Posted - 2011-04-29 : 03:17:16

i am facing one time zone problem in sql server 2008.
Problem : our sql server located in US-Canada.But i need to consider the time for chicago to all the date time transactions.
Question : How to get the Chicago time with out hadcoding the
time deference as "5" from UTCTime.

because
In Summer the time difference between UTCTime and Chicago is "-5" hours
In Winter the time difference between UTCTime and Chicago is "-6" hours

(In this case how to handle the Daylight Saving time)

-----------------------------------------------------
CREATE FUNCTION [dbo].[GetChicagoTime]()
RETURNS dateTime
BEGIN
RETURN DATEADD(SECOND,-155,DATEADD(HOUR,-5,GetUTCDate()))
END
-----------------------------------------------------------------

Thanks
K.Sudhakar

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 06:55:20

I want to break this up into parts. First, let us assume that all your servers were located in Eastern time zone (observes daylight savings time). Then, you could do the following to find the Chicago local-time. This would work correctly for daylight savings time and standard time.


declare @offsetFromChicago int;
set @offsetFromChicago = 1;
SELECT DATEADD(hour,DATEDIFF(hh,GETUTCDATE(),GETDATE())-1,GETUTCDATE()) as ChicagoTime;

Now part 2 - if your servers were located all over US and Canada, then you would need to indicate the offsetFromChicago for each location. So your function might be like this then:

CREATE FUNCTION [dbo].[GetChicagoTime]
	( @offsetFromChicago INT ) 
RETURNS DATETIME
as
BEGIN
	return DATEADD(hour,DATEDIFF(hh,GETUTCDATE(),GETDATE())-@offsetFromChicago,GETUTCDATE());
END

If you can't distinguish or program based on where the server is located, you will need some way of finding out the local time-zone information. You can get it from the OS using CLR procedures, if needed; there may be other ways.

You would also need to take into account places like Arizona or Hawaii that do not observe daylight savings time.

This of course, assumes that all time-zones are offset from UTC by full hours and not by fractional hours. If your servers are in places that are 5.5 hours ahead of GMT for example, you would need to use minutes rather than hours in your calculations.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources