Get TimeZone of SQL Server - 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

Transact-SQL (2008)

Get TimeZone of SQL Server

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2012-01-10 : 08:53:19

Hello All,

My final select query displays datetime data type field in this format.

2012-01-04 10:45:33.490

However, it should show it as one of the following:
1. Time zone ('2011-12-21 18:10:09.1234567 EST')
2. Offset ('2011-12-21 18:10:09.1234567 +05:30')

Our servers are on EST time zone. I don't want to hard code anything.

Can anyone tell me how to write a SQL to modify the exisiting date to show the format 1 or 2? Any help would be really appreciated.

Thanks much,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-10 : 09:28:36

[code]
select convert(varchar(30), getutcdate(), 121)
+ ' '
+ case when getutcdate() < getdate() then '+' else '-' end
+ convert(varchar(5), dateadd(minute, abs(datediff(minute, getutcdate(), getdate())), 0), 108)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-10 : 11:12:30

Are you trying to add the time zone offset (i.e. +5:00) or are you trying to convert to or from a UTC date and show the offset?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-10 : 11:22:58

How do you account for DST?

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2012-01-10 : 14:21:37

I am just adding the offset count (-5) for now.

When it is DST, I will have ot add the offset of that time.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-01-11 : 12:45:18

I am looking for something similar to this

Table has
TIME_ZONE
Africa/Addis_Ababa UTC_OFFSET 3

Target Time GMT PST
10:00 7:00 AM 11:00 PM

America/Mexico City UTC_OFFSET -6

Target Time GMT PST
10:00 4:00 AM 08:00 AM

System is at GMT time, so I need to display the time at 10:00 for the timezone, and also display the Pacific time and GMT

I have all the data in a spreadsheet for the OFFSET values and Timezones....

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-01-11 : 20:06:27

I got some where

DECLARE @DateTime2 DATETIME2(4) = SYSDATETIME()
, @DateTimeOffSet_1 DATETIMEOFFSET(4) = SYSDATETIME()
, @DateTimeOffset_2 DATETIMEOFFSET(4) = SYSDATETIMEOFFSET()

SELECT @DateTime2, @DateTimeOffSet_1, @DateTimeOffSet_2
, SWITCHOFFSET(@DateTimeOffset_2, '+08:00')

However, our sql is Pacific time, and need to run the report at 10:00 for every timezone, which I can determine by changing the +8 to +7, +6 etc..

But I want it to be 10:00 in the time zone not what the system time is when I run this script.

Any ideas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources