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

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

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

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

It works good, change all queries getdate() to dbo.dReturnDate(getdate()) dependy of your local time.

For example in Nicaragua is:

create FUNCTION dReturnDate( @dFecha as datetime)
returns DATETIME
as
begin
DECLARE @D AS datetimeoffset
SET @D = CONVERT(datetimeoffset, @Dfecha) AT TIME ZONE 'Central America Standard Time'
RETURN CONVERT(datetime, @D);
end

select dbo.dReturnDate(getdate())

Interesting way around it, but running it through a function could cause performance issues, as shown by Jonathon Kehayias elsewhere. It's frustrating. 

keiryliza : It works good, change all queries getdate() to dbo.dReturnDate(getdate()) dependy of your local time.

This sound like really bad idea! Using scalar function can reduce performance dramatically in many cases.

Instead simply use the inline code:

select CONVERT(datetime, CONVERT(datetimeoffset, getdate()) AT TIME ZONE 'Israel Standard Time')

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

You confuse two different services. The post is about Azure SQL Database and you speak about Azure Managed Instance 🙂

Jose_Manuel_Jurado : the default time zone on Azure SQL DB is UTC

Coordinated Universal Time (UTC) is a time standard and NOT a time zone, Greenwich Mean Time (GMT) is a time zone. This entire discussion and many of the documentations and the terms used by Microsoft should be speaking about GMT time zone and NOT about UTC.