Microsoft SQL Server

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?

You could do something like this:

declare @InputUtcDateTime datetime2 = '2016-06-24 06:30:18'

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

or

declare @InputUtcDateTime datetime2 = '2016-06-24 06:30:18'

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

My T-SQL

DECLARE @utcdate DATETIME = '2016-06-24 06:30:18.127'
SELECT DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),@utcdate) AS currentLocalTime;

Real i used

update [Order]
set StockDate =  DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),StockDate),
OrderDate = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),OrderDate),
ShipDate =  DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),ShipDate)

 

Published by

tuanitpro

Tôi là Lê Thanh Tuấn, và tôi chia sẻ những điều mình cho rằng nó là thú vị, hay giúp ích cho bạn!

Leave a Reply