About 15 years ago I was in charge of maintaining a procedure that had over 300 lines of code dedicated to handling time zone conversions gracefully. It had to account for the location of the servers to compensate for GETDATE() function. While keeping in mind the time zone for the source and destination users. Followed by adjusting for date formats like MDY vs DMY, time zones, daylight savings and NULLs, dates with / instead for – etc.
It was the stuff of nightmares. The guy who wrote it moved on and nobody wanted to tamper with the code because it was part of a crucial workflow. Luckily, I was too dumb to be daunted by the challenge in front of me and ended up writing a CLR function that performed all the complex logic using .NET and the underlying windows time zone capabilities. If you want to know more about that solution, checkout this video.
Thankfully SQL Server has come a long way since then. Today we have built-in functions that perform the job without developers having to harass the DBA to deploy CLR to a production environment. Let’s look at an actual example to see how much things have improved.
Problem
Users want to export data from a table, the export will use a time filter and anything post that time needs to be exported. The problem is users are located all over the world and they will use the time local to them. The data in the database is stored using GETDATE function and therefore uses local India time as the timestamps for each row.
Given the above we need to first convert the user input timestamp to IST timestamp and then apply the IST timestamp to the where condition of the select statement. The time and the users source time zone are provided by the application.
Solution
The native SQL Server datetimeoffset datetype supports timezone information and therefore can easily be used to add and deduct offsets as needed. By using the internal timezone information available in the system there is not need to worry about making sure DST etc is being accounted for.
The below screenshot shows how we can apply a Timezone to any datetime value provided to us
But wait a minute isn’t US Eastern Standard Time -5 from UTC. Yes , but right now it is in Day light saving time so SQL naturally adjusted for it.
DECLARE @userinputtime DATETIME = '2025-07-03 13:00:15'
DECLARE @userinputTz VARCHAR(100) ='US Eastern Standard Time'
DECLARE @UserformattedTime DATETIMEOFFSET
SET @UserformattedTime = (SELECT @userinputtime AT TIME ZONE @userinputTz)
SELECT @userinputtime AT TIME ZONE @userinputTz AS SourceTime,
@UserformattedTime AT TIME ZONE 'India Standard Time' AS DestinationTimeThe below screenshot shows the final journey from US Eastern after DST into IST, one line of code and you are all set.
Please Consider Subscribing
