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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-27 : 08:59:33
|
Steve writes "I am looking to create a function (UDF) that will convert a datetime in the past to a UTC datetime. Because I run a site that allows for entry across the country, I would like to get the datetime that was entered in UTC (including timezone changes e.g. Arizona and Indiana time that does not change). Here's some psuedocode that I think would work, but I do not have an idea on how to proceed in SQL2K:CREATE FUNCTION fn_TranslateToUTC(@dateInput DateTime, @strTimeZone varchar(8), @strState char(2))Returns DateTimeASBEGINDeclare @dateReturn as DateTime--CASE @strTimeZone (Eastern, Central, Mountain, Pacific) and set @dateReturn = DateDiff(h, +x, @dateInput) based on the time zone.--Based on the date, determine if the time of year is using daylight savings or standard time (This is where I am having difficulty in determining if the time of year is using daylight savings or standard based on the year.) END
Is there some function that tells me what the date ranges are for daylight savings time?Thanks in advance.Steve" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-27 : 12:24:24
|
| I have written a UDF to do this. Search the forums for udf_Timezone_Conversion. That is the main UDF. It also calls two other UDFs and a table that contains the timezones that the UDF supports. Let me know if you need any help with it. We've been running it in production for a couple of years now.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-04-27 : 12:36:55
|
| here is my new invention - I call it the wheel.-ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-27 : 12:45:08
|
| What do you mean by that? I know what you are referring to, but in this instance. I know of no other way at the database level to convert between timezones due to daylight savings time.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-04-27 : 14:03:07
|
| That was just me being cryptic.I guess my point is that many times people ask for stuff here, or code something up that has already been done before, and usually been done better. This seems to be the case with this request. All Steve needed to do was google this up a bit and he would have found your UDF.-ec |
|
|
|
|
|
|
|
|
UDF to convert DateTime to UTC