Converting UTC to BST: A T-SQL Guide to forgot about Daylight Saving Time

Converting UTC to BST DBAscrolls


We'll explore how to convert UTC (Coordinated Universal Time) to BST (British Summer Time) using T-SQL. Daylight Saving Time (DST) adjustments are crucial for handling time changes in regions that observe it. We'll dive into the provided T-SQL code, which calculates the current BST offset based on the UTC time and demonstrates the conversion process. This information will be valuable for developers and database administrators working with time-sensitive applications or data analysis in BST regions.

For more please visit
DECLARE @transitionStart DATETIME;
DECLARE @transitionEnd DATETIME;
DECLARE @bstOffset INT;

-- Get the current UTC time

-- Extract the year from the UTC time
SET @year = DATEPART(YEAR, @utcTime);

-- Calculate the start and end dates for the daylight saving transitions
SET @transitionStart = DATEADD(DAY, -(DATEPART(WEEKDAY, DATEFROMPARTS(@year, 3, 31)) + 7 - 1) % 7, DATEFROMPARTS(@year, 3, 31));
SET @transitionEnd = DATEADD(DAY, -(DATEPART(WEEKDAY, DATEFROMPARTS(@year, 10, 31)) + 7 - 1) % 7, DATEFROMPARTS(@year, 10, 31));

-- Calculate the BST offset for the current date
SET @bstOffset = CASE
    WHEN @utcTime >= @transitionStart AND @utcTime < @transitionEnd
        THEN 60 -- BST offset: +01:00 (1 hour ahead during daylight saving)
    ELSE 0 -- GMT offset: +00:00 (Greenwich Mean Time)

-- Convert UTC time to BST
SET @bstTime = DATEADD(MINUTE, @bstOffset, @utcTime);

-- Display the converted BST time
SELECT @bstTime AS [Current_BST_Time];

Please find GitHub link for above code on dbascrolls
Lince Sebastian

Indian MSSQL DBA thriving in database management - ensuring efficiency and smooth operations. Devoted father of two and avid Clash of Clans player, driven by strategic thinking. Football fuels my passion - cheering Kerala Blasters and Arsenal. I share my professional and life insights through my blog.


Post a Comment (0)
Previous Post Next Post