SQL Server 2008 – How to convert GMT(UTC) datetime to local datetime?

I have an insert proc that passes in GETDATE() as one of the values because each insert also stores when it was inserted. This is hosted on SQL Azure – which uses GMT.

Now, when I am receiving messages, I have the GMT date stored for each of them in their timestamp columns, how do I convert this to the local datetime for wherever you are when you are accessing my page?

Thanks.

Answer

You could do something like this:

declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'

declare @LocalDateTime datetime2 = dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), @InputUtcDateTime)
print @LocalDateTime

or

declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'

declare @LocalDateTime datetime2 = dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), @InputUtcDateTime)
print @LocalDateTime