question

MrYian-1536 avatar image
0 Votes"
MrYian-1536 asked Cathyji-msft edited

Create Azure SQL Server with my local time (UTC+8)

experts, i would like to create a azure SQL DB with my local time, when i query select getdate() which need to return UTC+8 result. please provide me the guidance,
thanks.

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cheong00 avatar image
0 Votes"
cheong00 answered MrYian-1536 commented

The only chance to set timezone is on instance creation only.

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview

And make sure you select "SQL managed instance" instead of "Azure SQL".

125251-azuretimezone.png



azuretimezone.png (44.1 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks for your URL, but it seen like that configuration GUI no longer valid in azure portal.

0 Votes 0 ·

See my update on that, the screen capture is fresh on my portal screen. :)

0 Votes 0 ·

thanks ! you make my day !
under database category have several SQL related , such like "SQL servers", "SQL managed instances" . my current setup just pick for the "SQL servers". not sure what different in between both.

anyway , this is really great ! finally i managed to see the GUI !

thanks !

0 Votes 0 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MrYian-1536 commented

Use the function "AT TIME ZONE (Transact-SQL)" to convert UTC timestamp to your local time zone.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Ola,
thanks for your fast response, it seen like this is SQL function to convert output data not from SQL itself.

it seen like UTC+0 is default and only setting for azure SQL DB

thanks.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered cheong00 converted comment to answer

This is really and Azure question, not a SQL Server question.

All AzureSQL instances are set to UTC and cannot be changed.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi Tom.
thanks for your fast response. UTC+0 is AzureSQL default. but as user , i do not think which is make sense since the solution is around the world, AzureSQL cannot expect each time when we query the datetime then need to call function to +8 in order to point to my location.

anyway , thanks for your response.

0 Votes 0 ·

I thought EntityFramework uses DateTimeOffset type to return datetime value by default (this type handles timezone difference), therefore is trivial to call .ToLocalTime() to get the time in local timezone.

Btw, Azure uses UTC with very good reason - to evade the problem with Daylight Saving Time, when for once every year, time goes backward and you have 2 time period with exactly the same timestamp, which is problematic to a lot of applications. When everything is stored in UTC at least it is a solvable problem.

0 Votes 0 ·

Hi Cheong00,
but my code is within SP, then which need a convertor before i process the data, especially SP return result with datetime data.

thanks.

0 Votes 0 ·
Show more comments

Again, it is not possible to change the time zone on AzureSQL. It is always going to be UTC time. It is not a "default", it is THE time zone and there is no way to change it.

It is very common to store all values in the database as UTC time and convert the time on the client side to the local time zone. You would be better off doing it that way when needed.

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered MrYian-1536 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi AndreasBaumgarten,
thanks for your fast response, but it seen like "Set the time zone through the Azure portal" no longer valid. i cant find those settings from SQL instance setup nor SQL DB creation. and i do not think by keep convert getdate with function in order to retrieve UTC+8 is good idea.

thanks.

0 Votes 0 ·