How can I get the correct offset between UTC and local times for a date that is before or after DST?

The best way to convert a non-current UTC date into local time, prior to SQL Server 2016, is to use the Microsoft .Net Common Language Runtime, or CLR.

The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary...

For one of the many examples, check out Harsh Chawla's blog post on the topic.

Unfortunately, there is nothing built-in prior to SQL Server 2016 that can handle this type of conversion, save for CLR-based solutions. You could write a T-SQL function which does something like this, but then you'd have to implement the date-change logic yourself, and I'd call that decidedly not easy.

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.

I always use this TSQL command.

-- the utc value 
declare @utc datetime = '20/11/2014 05:14'

-- the local time

select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc)

-- or if you're concerned about non-whole-hour offsets, use:

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @utc).

It is very simple and it does the job.