How to specify a local timezone on an Azure SQL server?

Question

I have a SQL server on Azure, and I would like to adjust the timezone setting to reflect my local time, which is UTC+8. The current getdate() function shows results in UTC. Is there a way to change the timezone setting to my local time? I am aware of the CONVERT(datetime, SWITCHOFFSET(datetime, DATEPART(TZOFFSET, datetime AT TIME ZONE 'Singapore Standard Time'))) method, but I'm wondering if there is an alternative.

Answer 1

Hi Syaza Liyana •,

Welcome to Microsoft Q&A forum.

As I understand, you want to specify local Timezone on Azure SQL Server Database.

Here you can make use of AT TIME ZONE function to convert UTC time to your local time zone.

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.

Refer: https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

UTC:

SELECT GETDATE() AT TIME ZONE 'UTC'

Local Timezone:

SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Singapore Standard Time' AS LocalTime;

You can also make a User Defined Function (UDF) to convert UTC to your local time.

This can make it easier to use the conversion in multiple places in your database.

CREATE FUNCTION dbo.ConvertToLocalTimeEightHour (@utcTime DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(HOUR, 8, @utcTime); -- Adjust 8 hours for UTC+8
END;
GO

-- Usage
SELECT dbo.ConvertToLocalTimeEightHour (GETDATE()) AS LocalTime;

Another way could be using DATEADD function:

SELECT DATEADD(HOUR, 8, GETDATE()) AS LocalTime;

Hope this should help in your case. Let us know if you have a different ask.

Thanks.