SQL Azure – Local DateTime

Here is a simple approach to get the local datetime i.e. GetDate() from SQL Azure instead of UTC datetime for data loads.

These scripts are mostly self-explanatory and in nutshell we are creating a function using a base table to hold the offset details (AEST) from here including daylight savings.

Step 1: Preparing Zone offset reference table and information

CREATE SCHEMA [Reference]
AUTHORIZATION [dbo]

CREATE TABLE [Reference].[ZoneTimeOffset](
 [StartDate] [datetime2](7) NOT NULL,
 [EndDate] [datetime2](7) NOT NULL,
 [OffSetMinutes] [int] NOT NULL,
 CONSTRAINT [ZoneTimeOffset_Date] PRIMARY KEY CLUSTERED 
(  
 [StartDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

INSERT INTO [Reference].[ZoneTimeOffset]

SELECT '2014-04-06 03:00' , '2014-10-05 01:59:59.999' , 600
UNION
SELECT '2014-10-05 02:00' , '2015-04-05 02:59:59.999' , 660
UNION
SELECT '2015-04-05 03:00' , '2015-10-04 01:59:59.999' , 600
UNION
SELECT '2015-10-04 02:00' , '2016-04-03 02:59:59.999' , 660
UNION
SELECT '2016-04-03 03:00' , '2016-10-02 01:59:59.999' , 600
UNION
SELECT '2016-10-02 02:00' , '2017-04-02 02:59:59.999' , 660
UNION
SELECT '2017-04-02 03:00' , '2017-10-01 01:59:59.999' , 600
UNION 
SELECT '2017-10-01 02:00' , '2018-04-01 02:59:59.999' , 660
UNION
SELECT '2018-04-01 02:00' , '2018-10-07 02:59:59.999' , 600

Step 2: Function to return local datetime

CREATE SCHEMA [System]
AUTHORIZATION [dbo]

CREATE FUNCTION [System].[GetDate]()
RETURNS datetime
AS
BEGIN
  DECLARE @return datetime2
         ,@utcdatetime datetime2 = GETUTCDATE()
  SELECT @return = ( SELECT DATEADD(minute, OffSetMinutes, @utcdatetime) AS [LocalDateTime]
  FROM [Reference].[ZoneTimeOffset]
 WHERE @utcdatetime BETWEEN StartDate and EndDate
)
RETURN @return
END

SELECT GETDATE() [GetDate] , GETUTCDATE() [GetUTCDate] , System.GetDate() [LocalGetDate]

Points to consider :
1. Used AEST offset details here but can be extendable to any time zone
2. Use System.GetDate() instead of GetDate() to get local datetime