Geek asks and answers

Local time for a specific time zone in SQL Server

I am migrating an existing application to Windows Azure. Our current solution relies heavily on the Windows time zone installed on SQL Server. We often use GETDATE()in our stored procedures and views. The problem is that SQL Azure works with the UTC time zone. I am trying to replace a method GETDATE()call with a method call, which will get time based on setting in the database what should be in the time zone. The problem I am facing is the switch to daylight saving time. Has anyone ever written a method in SQL that can return the current date / time and correctly set up for DST?

I assume that I will get a few answers that will say that I should use UTC once in all directions. This is apparently not an option for reasons that I would not better explain.

Any help / suggestions you can give me would be greatly appreciated. Thank.

+3
source share
2 answers

We decided to update the corresponding columns as DateTimeOffsetinstead DateTime.

Thanks to all who responded.

+1
source

I do not know if this will help you. But it does the registry, which is read in the SERVER registry. In the local context, you can determine the difference between the time zone of SERVERs and GMT and transfer the necessary calculations you need. A useful little trick that I picked up from one of the other forums (Credit is there).

Use Master
Go



DECLARE @ZONEDIFF INT

EXECUTE dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','ActiveTimeBias', @ZONEDIFF OUT

SELECT getdate() AS MyTime, dateadd(minute, @ZONEDIFF, getdate() ) as SERVERZONE

, , .

Mac

0

More articles:


All Articles