Menu
We are dealing with an application that needs to handle global time data from different time zones and daylight savings time settings. The idea is to store everything in UTC format internally and only convert back and forth for the localized user interfaces. Does the SQL Server offer any mechanisms for dealing with the translations given a time, a country and a timezone?
This must be a common problem, so I'm surprised google wouldn't turn up anything usable.
Any pointers?
916
SQL Server GETUTCDATE() Function The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format.
2 Answers. SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime. SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);
Add the local time offset to the UTC time. For example, if your local time offset is -5:00, and if the UTC time is shown as 11:00, add -5 to 11. The time setting when adjusted for offset is 06:00 (6:00 A.M.). Note The date also follows UTC format.
SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETUTCDATE()), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS LOCAL_IST; Here, the GETUTCDATE() function can be used to get the current date and time UTC.
This works for dates that currently have the same UTC offset as SQL Server's host; it doesn't account for daylight savings changes. Replace YOUR_DATE with the local date to convert.
SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);
68
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
