Formatting Local Datetime to UTC in SQL Server

Introduction

We often come across requirements in our applications where we are required to store datetime in standard format. Such requirements mainly arise when an application is used by users across multiple geographies. This article intends to describe a simple and elegant way of converting local datetime to UTC and store it in the DB.

Background

We had an application where data was entered by users from multiple geographies. The challenge was to store datetime in a standard format irrespective of time zone. For each entry when the time exceeded 1 hour, a file was to FTP'ed.

Using the code

This code can be directly used inside any proc in MS SQL Server.

Create Function dbo.[ChangeLocalTimeToUTC]
(
    @CurrentServerDateTime as DateTime,
    @CurrentUTCDateTime as DateTime,
    @LocalTimeToChange as Datetime
)
Returns DateTime

Begin
    Declare @ServerTime Datetime    
    Set @ServerTime = @CurrentServerDateTime

    Declare @UTCTime Datetime
    Set @UTCTime = @CurrentUTCDateTime

    Declare @Offset int
    set @Offset = DateDiff(second,@ServerTime,@UTCTime)

    Declare @ConvertedUTCTime Datetime
    Set @ConvertedUTCTime = DateAdd(second,@Offset,@LocalTimeToChange)

    Return @ConvertedUTCTime 
End

Points of Interest

This requirement highlighted a peculiar problem faced by the application that span multiple geographies in the currently globalized world. It also brought to the fore the use of standard timezone neutral format.