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

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.

  [1]: http://tsqltoolbox.codeplex.com