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.
