Lesson Learned #4: Modifying the default time zone for your local time zone.

Published Mar 13 2019 06:13 PM 28.7K Views
First published on MSDN on Jul 27, 2016


Currently, the default time zone on Azure SQL DB is UTC . Unfortunately, there is not possible to change by server configuration or database configuration.

All Azure services use UTC time zone settings, regardless of their physical location. This also applies for SQL Azure, so if you need a custom time zone you'll have to manage that in the middle/front end tier.

One suggestion that we have is to review the new date/time function that AZURE SQL DATABASE provided as AT TIME ZONE and datetimeoffset you could find out the different time zones running the query: select * from sys.time_zone_info

For example, we could create the following function:

CREATE FUNCTION dReturnDate( @dFecha as datetime)


returns DATETIME


as


begin


DECLARE @D AS datetimeoffset


SET @D = CONVERT(datetimeoffset, @Dfecha) AT TIME ZONE 'W. Europe Standard Time'


RETURN CONVERT(datetime, @D);


end


Running the query DBO.dReturnDate(getdate()) you could observe the results. Please, verify the timezone that you are working on with select * from sys.time_zone_info.

3 Comments
Occasional Visitor

Thank you very much, just what you were looking for!

Occasional Visitor

I created a simple function that returns the correct UK time whether in DST or not.
This can be adapted for different time zones.

CREATE FUNCTION [dbo].[f_CurrentDateTime]() RETURNS DATETIME AS
BEGIN
RETURN DATEADD(HOUR,CONVERT(INT,(SELECT is_currently_dst FROM sys.time_zone_info WHERE 1=1 AND NAME = 'GMT Standard Time')),GETDATE())
END

Occasional Visitor

If there is an absolute need to change the timezone, there is a workaround mentioned in the docs.

 

  • The time zone of the existing managed instance can't be changed. As a workaround, create a new managed instance with the proper time zone and then either perform a manual backup and restore, or what we recommend, perform a cross-instance point-in-time restore.

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

 

%3CLINGO-SUB%20id%3D%22lingo-sub-368798%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%234%3A%20Modifying%20the%20default%20time%20zone%20for%20your%20local%20time%20zone.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368798%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2027%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Currently%2C%20the%20default%20time%20zone%20on%20%3CSTRONG%3E%20Azure%20SQL%20DB%20is%20UTC%20%3C%2FSTRONG%3E%20.%20Unfortunately%2C%20there%20is%20not%20possible%20to%20change%20by%20server%20configuration%20or%20database%20configuration.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20All%20Azure%20services%20use%20UTC%20time%20zone%20settings%2C%20regardless%20of%20their%20physical%20location.%20This%20also%20applies%26nbsp%3Bfor%26nbsp%3BSQL%20Azure%2C%20so%20if%20you%20need%20a%20custom%20time%20zone%20you'll%20have%20to%20manage%20that%20in%20the%20middle%2Ffront%20end%20tier.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20One%20suggestion%20that%20we%20have%20is%20to%20review%26nbsp%3Bthe%20new%20date%2Ftime%20function%20that%20AZURE%20SQL%20DATABASE%20provided%20as%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fmt612795.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20AT%20TIME%20ZONE%20%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb630289.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20datetimeoffset%20%3C%2FA%3E%20you%20could%20find%20out%20the%20different%20time%20zones%20running%20the%20query%3A%20select%20*%20from%20sys.time_zone_info%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20For%20example%2C%20we%20could%20create%20the%20following%20function%3A%20%3CBR%20%2F%3E%3CP%3ECREATE%20FUNCTION%20dReturnDate(%20%40dFecha%20as%20datetime)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereturns%20DATETIME%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eas%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40D%20AS%20datetimeoffset%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40D%20%3D%20CONVERT(datetimeoffset%2C%20%40Dfecha)%20AT%20TIME%20ZONE%20'W.%20Europe%20Standard%20Time'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERETURN%20CONVERT(datetime%2C%20%40D)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eend%3C%2FP%3E%3CBR%20%2F%3E%20Running%20the%20query%26nbsp%3BDBO.dReturnDate(getdate())%20you%20could%20observe%20the%20results.%20Please%2C%20verify%20the%20timezone%20that%20you%20are%20working%20on%20with%20select%20*%20from%20sys.time_zone_info.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FDIV%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368798%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2027%2C%202016%20Currently%2C%20the%20default%20time%20zone%20on%20Azure%20SQL%20DB%20is%20UTC.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368798%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eat%20time%20zone%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edefault%20time%20zone%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Egetdate%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elocal%20time%20zone%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esys%20time_zone_info%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eutc%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1505782%22%20slang%3D%22es-ES%22%3ERe%3A%20Lesson%20Learned%20%234%3A%20Modifying%20the%20default%20time%20zone%20for%20your%20local%20time%20zone.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1505782%22%20slang%3D%22es-ES%22%3E%3CP%3EThank%20you%20very%20much%2C%20just%20what%20you%20were%20looking%20for!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1910170%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%234%3A%20Modifying%20the%20default%20time%20zone%20for%20your%20local%20time%20zone.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1910170%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20simple%20function%20that%20returns%20the%20correct%20UK%20time%20whether%20in%20DST%20or%20not.%3CBR%20%2F%3EThis%20can%20be%20adapted%20for%20different%20time%20zones.%3C%2FP%3E%3CP%3ECREATE%20FUNCTION%20%5Bdbo%5D.%5Bf_CurrentDateTime%5D()%20RETURNS%20DATETIME%20AS%3CBR%20%2F%3EBEGIN%3CBR%20%2F%3ERETURN%20DATEADD(HOUR%2CCONVERT(INT%2C(SELECT%20is_currently_dst%20FROM%20sys.time_zone_info%20WHERE%201%3D1%20AND%20NAME%20%3D%20'GMT%20Standard%20Time'))%2CGETDATE())%3CBR%20%2F%3EEND%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1927727%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%234%3A%20Modifying%20the%20default%20time%20zone%20for%20your%20local%20time%20zone.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1927727%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20there%20is%20an%20absolute%20need%20to%20change%20the%20timezone%2C%20there%20is%20a%20workaround%20mentioned%20in%20the%20docs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CEM%3EThe%20time%20zone%20of%20the%20existing%20managed%20instance%20can't%20be%20changed.%20As%20a%20workaround%2C%20create%20a%20new%20managed%20instance%20with%20the%20proper%20time%20zone%20and%20then%20either%20perform%20a%20manual%20backup%20and%20restore%2C%20or%20what%20we%20recommend%2C%20perform%20a%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fpoint-in-time-restore%3Ftabs%3Dazure-portal%23restore-an-existing-database%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecross-instance%20point-in-time%20restore%3C%2FA%3E.%3C%2FEM%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Ftimezones-overview%23limitations%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Ftimezones-overview%23limitations%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Mar 13 2019 06:13 PM
Updated by: