Azure SQL Database Time Error
I have a .net application running on an Azure server that is configured for PST and it connects to an Azure SQL Server set to UTC. I use the getDate () function in many of my stored procedures. Moving to the time zone will be a critical issue and should keep them in sync. Using the DateAdd (hour, -8, getDate ()) function ignores daylight savings time. I was wondering if there is a better way to handle this.
source to share
Change all columns datetime
to columns datetimeoffset(7)
. These columns store time zone information with date and time.
Then change your calls GETDATE()
to SYSDATETIMEOFFSET()
.
Finally, you can convert the stored time information using the keyword AT TIME ZONE
:
SELECT *,
[Time] AT TIME ZONE 'Pacific Standard Time' AS PstTime
FROM TheTable
- I tried to store the values
datetime
always in UTC (or any other arbitrary zone) and ALWAYS run problems. After switching todatetimeoffset(7)
almost all of my problems were resolved.
source to share