Time zone Settings - 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 2005 Forums

Transact-SQL (2005)

Time zone Settings

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-04-11 : 21:46:20

Hi All,

I have a Db server in US(with EST time settings) and application server is in Sydney(Australia.I want all the DB entries to be the sydney timings with automatic day light saving.SQL server is 2008 Enterprise edition.

Thanks,
Gangadhara MS
SQL Developer and DBA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-04-12 : 10:26:54

Is there some reason why setting the DB server to Sydney time would not work?

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-04-12 : 12:12:20

I'd recommend storing all times in UTC and then letting application code handle the conversion.

elsasoft.org

somannameti
Starting Member

2 Posts

Posted - 2012-04-13 : 00:29:40

is there any ways to calculate day light timing for particular year in sql

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-04-13 : 02:51:01

yes. can use dateadd function. the tricky part is to know how many hours to add/subtract from UTC. to know this, you have to know a few things:

1. where geographically are you located? the DST rule vary by space.

In Mexico it's particularly perverse because the country is not uniform. The areas that border the USA follow the USA rules, and the rest of the country follows a different set of rules: http://www.timeanddate.com/news/time/north-mexico-dst-change.html

EU turns DST on and off at different times compared to USA - another example of how DST rules vary by space across the globe.

2. when are you located? the DST rules vary by time. For example Indiana prior to April 2006 did not observe DST, but now they do.

elsasoft.org

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-04-13 : 15:52:46

quote:
In Mexico it's particularly perverse because the country is not uniform.

It's pretty perverse in the US, also.

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-04-14 : 00:42:33

well it was perverse in US before 2006 because of Indiana. But they have been brought in line. Now it's reasonable here, except for Arizona and Hawaii maybe since they don't observe DST.

So as long as you don't need to convert dates prior to April 2006 in USA, it's fairly straightforward assuming you know what timezone you are in.

Easiest is China - the whole giant country is one timezone! It's Beijing time everywhere in China.

elsasoft.org

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-04-15 : 23:00:43

I am stuck here could you please assist with some sample code snippet for any 2 countries time zone which you guys are familiar

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-04-17 : 01:11:06

can somebody please help here.

Thanks,
Gangadhara MS
SQL Developer and DBA

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-17 : 10:54:12

What are you asking for exactly? This is a very painful problem and there is no easy solution for it. You can get your hands the TZ/Olsen database (http://en.wikipedia.org/wiki/Tz_database) and use that to calcualte the correct offset based on date and locale.

Maybe this will help?

SELECT 
	SYSUTCDATETIME() AS UtcTime, 
	SYSDATETIME()AS LocalTime, 
	SYSDATETIMEOFFSET() AS UtcOffset, 
	DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) AS TimeZoneOffset,
	DATEADD(MINUTE, -1 * DATEPART(TZOFFSET, SYSDATETIMEOFFSET()), SYSDATETIME()) AS LocalToUtcConversion

EDIT: Nevermind, I thought I was in the 2008 forum.

Here is a 2005 Version:

SELECT 
	GETUTCDATE() AS UtcTime, 
	GETDATE() AS LocalTime, 
	DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) AS TimeZoneOffset,
	DATEADD(MINUTE, -1 * DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), GETDATE()) AS LocalToUtcConversion,
	DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), GETUTCDATE()) AS UtcToLocalConversion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources