We ultimately needed the UTC dates in our SQL Server database to line up with an algorithm in a 3rd party program that used the Java API to convert from UTC to local time. It worked for almost all of our data, but then I later realized that his algorithm only works for dates as far back as April 5, 1987, and we had some dates from the 1940s that still didn't convert properly. I wound up using a T-SQL approach from the second answer by Bob Albright to clean up a mess caused by a data conversion consultant. RETURN DATEADD(hh, are a couple of good answers to a similar question asked on Stack Overflow. SET = CASE WHEN BETWEEN AND THEN 1 ELSE 0 END WHILE (DATENAME(dw, 'sunday') SET = DaylightSavingOffset WHILE (DATENAME(dw, 'sunday') SET = DATEADD(day, First Possible DST EndDate SET = DATEADD(hh, out the DST Offset for the UDT Datetime Set the Timezone Offset (NOT During DST ) It's not perfect and probably won't work for many cases such has half-hour or 15-minute TZ offsets (for those I'd recommend a CLR function like Kevin recommended), however it works well enough for most generic time zones in North America. In my case, our SQL server uses EST, which is GMT - 5 The only thing you need to modify is the variable at the top to set it to the Timezone offset of the SQL server running this function.
I found this answer on StackOverflow that provides a User Defined Function that appears to accurately translate the datetimes I don't have a MSSQL 2005 available for testing, but it should work with MSSQL 2005, too, then.
#Utc time offset full
But as the full source code is provided you can easily adjust the tables and UDFs by replacing them by DATETIME. Unfortunately, it is supported for SQL Server 2008 or later only because of newer data types (DATE, TIME, DATETIME2). This will return the converted local datetime value. ' 00:55:00' - the original UTC datetime you want to convert Europe Standard Time', - the target local timezone In your example, you can use the following sample: SELECT. And it has full DST (daylight saving time) support.Ī list of all supported timezones can be found in table "DateTimeUtil.Timezone" (provided within the T-SQL Toolbox database). It offers easy datetime conversion UDFs using plain T-SQL (no CLRs) in addition with pre-filled configuration tables out of the box.
#Utc time offset free
It’s open source and completely free to use. I have developed and published the T-SQL Toolbox project on codeplex to help anybody who struggles with datetime and timezone handling in Microsoft SQL Server.
0 kommentar(er)
