Azure SQL database time issue

Question

I have a .net application running on a Azure server which is set to PST and it connects to a Azure SQL server set to UTC time. I use the function getDate() in a lot of my stored procedures. Going forward time zone is going to be a critical issue and need to have them both in sync. Using the function DateAdd(hour, -8, getDate()) does not account for daylight savings time. I was wondering if there was a better way to handle this.

Answer 1

Change all your datetime columns to datetimeoffset(7) columns. These columns store timezone information with the date and the time.

Next change your GETDATE() calls to SYSDATETIMEOFFSET().

Finally, you can convert the stored time information using the AT TIME ZONE keyword:

SELECT   *,
         [Time] AT TIME ZONE 'Pacific Standard Time' AS PstTime
FROM     TheTable

• I've tried storing datetime values always in UTC (or any other arbitrary zone) and have ALWAYS run into issues. After switching to datetimeoffset(7) nearly all my problems have been resolved.