Timezone conversions using Microsoft timezones.

Question

Post reply

Timezone conversions using Microsoft timezones.

rclare@factset.com

SSC-Addicted

Points: 406
More actions
July 16, 2008 at 12:34 pm

#210012

Has anyone been able to convert time from a given timezone using the Microsoft format like "(GMT-06:00) Central Time (US & Canada)" to UTC?
I know someone has written the code for this in C# and figured it wouldn't be too difficult to get it into the CLR and use it from SQL. I havne't used the CLR in SQL yet though. Are there restrictions preventing code written in SQL CLR from accessing operating system functions or the registry? I believe that's how the C# code I'm refering to was done.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

This might help you some, but here is a function that gets the current UTC Time:

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlDateTime UTCTimeConversion()

{

return new SqlDateTime(TimeZone.CurrentTimeZone.ToUniversalTime(DateTime.Now));

}

You can however get the output of the above with the getutcdate() function, so it is probably pretty useless. If this doesn't help, please explain with an example what you trying to do.

rclare@factset.com SSC-Addicted Points: 406 More actions · Answer 2

Basically I need a function to convert datetime to UTC like so:

datetime convertToUTC(datetime, string) where the string is a timezone like "(GMT-06:00) Central Time (US & Canada)"

I have a requirement to store the time "as entered" but to provide the data to another database as UTC. I'd really rather not store both UTC and local time.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Is it always going to be based on the SQL Servers Local Time Zone?

rclare@factset.com SSC-Addicted Points: 406 More actions · Answer 4

rclare@factset.com

SSC-Addicted

Points: 406

July 17, 2008 at 8:00 am

#844408

No, it could be any timezone.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

Rather than store the name, why don't you store the offset from UTC? Or you can create a lookup table with the Name/Offset pair and use an ID field as an FK. This actually makes more sense to me than a CLR EXTERNAL_ACCESS assembly would to read the registry because it is all safe, and internal TSQL Available.

rclare@factset.com SSC-Addicted Points: 406 More actions · Answer 6

That table sounds fairly difficult to build. I'd have to have date ranges for each timezone to take daylight savings into account. Is it easier than I'm thinking?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Ryan,

I haven't ever actually implemented one, but I am certain that it would be fairly complex, given the fact that there is no international standard, and not all timezones participate in DST. In fact in the US, there is at least one state that doesn't participate in DST, but I can't think of it off hand.

The SQL Programability Blog has an article that offers some solutions:

http://blogs.msdn.com/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

Give it a look and see if that helps some.