Posts

Showing posts from March 31, 2019

How to get the start and end of a day in T-SQL when daylight savings is enabled?

Image
0 1 I am currently working on a SQL Server script where we collect aggregated data on a daily basis for last 'n' days. In order to do this, we create a temp table with the start time and end time for all days for the last 'n' days. The code is as below. DECLARE @Tbl TABLE (Begin DATETIME, End DATETIME) DECLARE @i INT = 1 DECLARE @begin DATETIME = DATEADD(DD, DATEDIFF(DD, 1, GETDATE()), 0) DECLARE @end DATETIME = DATEADD(DD, 1, @begin) WHILE @i <= @n INSERT INTO @Tbl SELECT @begin, @end SET @end = @begin SET @begin = DATEADD(DAY, -1, @end) SET @i = @i + 1 END We convert these dates to UTC by calling an inbuilt function which does the operation correctly as expected but throws an error for November 4th 2018 00:00:00 as this is not a valid local time. The issue wit