Home Dashboard Directory Help
Search

time zone conversion function (SQL 2008) by DB007


Status: 

Closed
 as Won't Fix Help for as Won't Fix


22
0
Sign in
to vote
Type: Suggestion
ID: 308563
Opened: 11/5/2007 1:26:12 AM
Access Restriction: Public
2
Workaround(s)
view

Description

With the new UTC storing of time-information, we may require a conversion to user local time. Currently this can only be performed by a CLR (untrusted as not a MS CLR).

Time information can be stored as UTC, with a user-local time-zone indicator.
There is a requirement to show local-time zone information for all users of a system (not the UTC) - requiring a parsing function of <dateinput>, <destinationtimezone> from the generic server timezone.

Details
Sign in to post a comment.
Posted by DB007 on 3/17/2011 at 3:40 AM
Need I say that other RDBMS Vendors have this support already?

This requirement will grow as time progresses (and we have more global connected systems with cross-zone reporting requirements) - the reason for wont' fix is?
Posted by DB007 on 8/22/2008 at 1:33 AM
This information is available, for historical information - try looking for: Olson Time Zone database - public domain database of this information.
Posted by James Mahood on 4/14/2008 at 5:08 PM
DateTimeOffset wasn't what I was hoping for either. File Time works perfectly. It is always stored in UTC and when you look at a File Time is converted to your local time where you are observing the File Time. When I look at a DateTimeOffset (UTC) in EST and see 17:00 I expect to see 15:00 when I look at the same time in MST. Showing 17:00 - 6:00 in both cases is confusing. I was hoping Excel, the report writer, and Server Managment studio would show the time corrected for the observers local time zone like the Explorer shows file times.
Posted by DB007 on 1/4/2008 at 9:08 AM
For example:
MyTimeZoneConversionFunction (getdate(),'UK-GMT','USA-EST')
--> convert current value parsed in, and output usa est time for that date, given the fact that input was UK-GMT.

MyTimeZoneConversionFunction (getdate(),'UK-GMT','AUS-EST')
--> convert current value parsed in, and output Australia EST time for that date, given the fact that input was UK-GMT.

MyTimeZoneConversionFunction (getdate(),'AUS-EST','AUS-CTR')
--> convert current value parsed in, and output Australia CTR time for that date, given the fact that input was AUS-EST.


MyTimeZoneConversionFunction (Value,SourceTz,DestTz)
Syntax:
Value - Source date/time value (will accept just date or time or a combination of both)
SourceTz: Source time zone
DestTz: Destination time zone
Posted by DB007 on 12/17/2007 at 1:21 AM
Not quite what I'm looking for, for the following reason:
If you have DATETIMEOFFSET values and want to know the local datetime value in the stored time zone,
--This is the problem, the DATETIMEOFFSET valeus are often unknow. (Only the server datetimeoffset can be derived).
What will be known is the Timezone that the person is in.

The concept is to take "timezone" rather than a arbitary time value. This way the timezone can have daylight summer time rules applied.

What this requires is a lookup of timezones, e.g. EST, GMT, WST, ... and conversion of timezone input from base server time for the input date (with summertime/wintertime rules).

A simple +- from a base timestamp does not suffice.
Posted by Microsoft on 12/13/2007 at 1:49 PM
With the new date/time type support added in SQL Server 2008, this can be done through the new time zone aware & perserved UTC type (DATETIMEOFFSET) and corresponding built-ins.

Here are some examples:
1. If you have DATETIMEOFFSET values and want to know the local datetime value in the stored time zone, you can use the CAST/CONVERT built-in. For example, SELECT CONVERT (datetime2, [dtoffset], 1) (note, style 0 is the default and will return UTC values)
2. If you have DATETIMEOFFSET values and want to know the local datetime value in any given time zone, you can use the combination of SWITCHOFFSET and CAST/CONVERT built-ins. For example, SELECT CONVERT ( datetime2, SWITCHOFFSET([dtoffset], ’hh:mm’),1)
3. You can get the database/server DATETIMEOFFSET value by calling SYSDATETIMEOFFSET built-in and then apply #1 and #2 in order to get the local datetime in various time zone.

I wish this can help in terms of what you want.

thanks-michael



Posted by DB007 on 11/8/2007 at 2:55 AM
I believe this could be added via a service pack (as a functionality improvement) at a later date if it does not ship with the product as the udf would have few core engine dependancies.
Posted by DB007 on 11/5/2007 at 7:03 AM
The reason for removing the untrusted CLR is that many secure installations will not support untrusted non-MS CLRs, resulting in this query having to be performed by an application server or extensive TSQL lookups/tables for reporting services functions.
Sign in to post a workaround.
Posted by shell_l_d on 7/15/2010 at 4:31 PM
I was googling for an SQL TimeZone Conversion utility/function for our situation & found this article with source code:

SQL 2005 Time Zone Conversion Functions - CodeProject
http://www.codeproject.com/KB/database/SQL2005_TIME_ZONES.aspx
Posted by Zareer on 3/18/2008 at 7:49 AM
I have taken all the time zone info that you see if you double click the clock in the task bar and created a look up table with it. I have then had to write T-SQL functions to do the exact thing you are requesting. However, this is not a great workaround as the lookup table is not updated when Windows update adds new TimeZone rules as countries keep changing...