Category: MS Azure Dev Ops

Here I create a main table which can be used to translate times into a UTC value using a function I store here. It should be noted that if you have a field with variable type set to datetime and you set the default to GetDate() – the server will create a UTC value on record creation but time is set on the server and with SQL Azure you don’t have an option to change it.

CREATE TABLE [dbo].[MainTable](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[storeddatetime] [datetime] NULL,
	[timezonename] [nvarchar](128) NULL
PRIMARY KEY CLUSTERED 
(
	[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MainTable] ADD  CONSTRAINT [DF_MainTable_storeddatetime]  DEFAULT (getdate()) FOR [storeddatetime]
GO

Next we create a timezone table

CREATE TABLE [dbo].[TimeZoneTable](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[timezonename] [nvarchar](128) NULL,
PRIMARY KEY CLUSTERED 
(
	[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And we can populate this from sys.time_zone_info table.

INSERT INTO TimeZoneTable (timezonename)
SELECT name FROM sys.time_zone_info;

We can create a function to convert this datetime value knowing the timezone into UTC is as follows

CREATE FUNCTION dbo.fn_ConvertToUTC (@localDatetime DATETIME, @timeZone NVARCHAR(128))
RETURNS DATETIME
AS
BEGIN
    DECLARE @utcDatetime DATETIME;
    SET @utcDatetime = @localDatetime AT TIME ZONE @timeZone AT TIME ZONE 'UTC';
    RETURN @utcDatetime;
END;

Now runnig the following query on some example data we get the following

SELECT 
    pkid, 
    storeddatetime, 
    timezonename, 
    dbo.fn_ConvertToUTC(storeddatetime, timezonename) AS utcValue
FROM 
    MainTable;

And here is a function that will convert to local time from utc

CREATE FUNCTION dbo.fn_ConvertToLocal (@utcDatetime DATETIME, @timeZone NVARCHAR(128))
RETURNS DATETIME
AS
BEGIN
    DECLARE @localDatetime DATETIME;
    SET @localDatetime = @utcDatetime AT TIME ZONE 'UTC' AT TIME ZONE @timeZone;
    RETURN @localDatetime;
END;

Javascript option
The getTimezoneOffset() method of the Date object in JavaScript calculates the difference, in minutes, between the local time zone and the UTC time zone 1. The method returns a positive value if the local time zone is behind UTC, and a negative value if the local time zone is ahead of UTC 1.

The method works by subtracting the local time zone offset from UTC time. For example, if the local time zone is 5 hours behind UTC, then the method will return 300 (5 * 60) 2