Basic alternative to fn_LocalTimeToUTC

Question

My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime.

How would I go about getting the same functionality that these two functions offer manually and directly in SQL without using these functions?

To try to simplify it further, All our users are in the UK.

Answer 1

The difference between local time and UTC time can be reliably calculated by looking at the difference between GETDATE() and GETUTCDATE(), assuming the server operating system is configured in whatever the desired "local" time is (i.e. if local time for you is Central European Time, then the server must be configured to be in Central European Time).

As an example, consider the following:

GO
DROP FUNCTION dbo.LocalTimeToUTC;
GO
CREATE FUNCTION dbo.LocalTimeToUTC
(
    @LocalTime datetime
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT UTCTime = DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), @LocalTime)
)
GO

DROP FUNCTION dbo.UTCToLocalTime;
GO
CREATE FUNCTION dbo.UTCToLocalTime
(
    @UTCTime datetime
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT LocalTime = DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @UTCTime)
)
GO


SELECT *
FROM (
    VALUES ('2020-09-10T08:47:00')
    )
    v(LocalDate)
CROSS APPLY dbo.LocalTimeToUTC(v.LocalDate) u
CROSS APPLY dbo.UTCToLocalTime(u.UTCTime) l

The output looks like:

╔═════════════════════╦═════════════════════════╦═════════════════════════╗
║      LocalDate      ║         UTCTime         ║        LocalTime        ║
╠═════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2020-09-10T08:47:00 ║ 2020-09-10 13:47:00.000 ║ 2020-09-10 08:47:00.000 ║
╚═════════════════════╩═════════════════════════╩═════════════════════════╝

For clarity, this only works assuming all your clients are in the same time zone, and the server is installed to match your desired "local" time zone.

GETUTCDATE() is supported in all versions of SQL Server since at least 2008.

It's also worth noting that if you use this on datetime values saved in a table, and your local timezone uses daylight savings time, where the difference between local and UTC time varies during the year, you would need to adjust for the difference between standard time and daylight savings time.

A more reliable method of storing datetime data in SQL Server when you're concerned about converting between time zones is to the use datetimeoffset data type instead of datetime, and using the AT TIME ZONE modifier when querying the stored values.

An example of how that works:

DECLARE @dt datetimeoffset;
SET @dt = GETDATE() AT TIME ZONE 'Central Standard Time';

SELECT @dt;

(No column name)
2020-10-19 10:26:37.8100000 -05:00

To get the time at UTC:

SELECT @dt AT TIME ZONE 'UTC';

2020-10-19 15:26:37.8100000 +00:00