time zone conversion function (SQL 2008) - by DB007

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


22
0
Sign in
to vote
ID 308563 Comments
Status Closed Workarounds
Type Suggestion Repros 5
Opened 11/5/2007 1:26:12 AM
Access Restriction Public

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.

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 Michael [MSFT] 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.