Check out T-SQL Toolbox on CodePlex: http://tsqltoolbox.codeplex.com

It offers easy datetime conversion UDFs using plain T-SQL with additional configuration tables out of the box!

In your example, you can use the following sample:

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

This will return the converted local datetime value.

A list of all supported timezones can be found in table "DateTimeUtil.Timezone" (provided within the T-SQL Toolbox database).

Unfortunately, it is supported for SQL Server 2008 or later because it uses newer data types (DATE, TIME, DATETIME2). But as the full source code is available you can easily adjust the tables and UDFs by replacing them by DATETIME.

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. It’s open source and completely free to use.

It offers easy datetime conversion UDFs using plain T-SQL (no CLRs) in addition with pre-filled configuration tables out of the box. And it has full DST (daylight saving time) support.

A list of all supported timezones can be found in table "DateTimeUtil.Timezone" (provided within the T-SQL Toolbox database).

In your example, you can use the following sample:

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

This will return the converted local datetime value.

Unfortunately, it is supported for SQL Server 2008 or later only because of newer data types (DATE, TIME, DATETIME2). But as the full source code is provided you can easily adjust the tables and UDFs by replacing them by DATETIME. I don't have a MSSQL 2005 available for testing, but it should work with MSSQL 2005, too, then. In case of questions, just let me know.

Check out T-SQL Toolbox on CodePlex: http://tsqltoolbox.codeplex.com

It offers easy datetime conversion UDFs using plain T-SQL!

Check out T-SQL Toolbox on CodePlex: http://tsqltoolbox.codeplex.com

It offers easy datetime conversion UDFs using plain T-SQL with additional configuration tables out of the box!

In your example, you can use the following sample:

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

This will return the converted local datetime value.

A list of all supported timezones can be found in table "DateTimeUtil.Timezone" (provided within the T-SQL Toolbox database).

Unfortunately, it is supported for SQL Server 2008 or later because it uses newer data types (DATE, TIME, DATETIME2). But as the full source code is available you can easily adjust the tables and UDFs by replacing them by DATETIME.