Day light savings time is resulting in incorrect dates in database

Question

BACKGROUND:

I have an issue with the day light savings time change. Any records entered into the database (side note: I have no access to the script/code which enters this data to fix it) between 2021-03-28 and 2021-10-31 get entered into the database with the incorrect date. For example:

Records entered in on 2021-03-26 end up in the database as 2021-03-26 00:00:00, which is correct.

Records entered in on 2021-03-29 end up in the database as 2021-03-28 23:00:00 which is incorrect.

So when I try to search for records entered in on2021-03-26, the query works fine, but if I try to search for records entered in on 2021-03-29, it returns records from the wrong date because of the hour change.

---

SAMPLE DATA:

ColDate, ColName
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-28 23:00:00, SomeName -- INCORRECT DATE
2021-03-28 23:00:00, SomeName -- INCORRECT DATE
2021-03-28 23:00:00, SomeName -- INCORRECT DATE

---

WORKING EXAMPLE:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-26 00:00:00'
SET @EndDate = '2021-03-26 23:59:59'

SELECT *
FROM tblName
WHERE ColDate BETWEEN @StartDate AND @EndDate

The above will return:

ColDate, ColName
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE

---

NONE WORKING EXAMPLE:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-29 00:00:00'
SET @EndDate = '2021-03-29 23:59:59'

SELECT *
FROM tblName
WHERE ColDate BETWEEN @StartDate AND @EndDate

The above will return nothing from the sample data.

---

QUESTION:

How do I get around this issue? As mentioned earlier, I have no control of the data entry and the developers have no interest in fixing the issue.

Do I need to use IF statements and check if the date is between 2021-03-28 and 2021-10-31 and adjust the date by 1 hour accordingly? Or is there a better way to resolve this?

---

UPDATE - POSSIBLE SOLUTION:

The following query seems to work (2021-03-26):

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-26 00:00:00'
SET @EndDate = '2021-03-26 23:59:59'

SELECT
    ColDate AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time',
    ColName
FROM tblName
WHERE ColDate  BETWEEN @StartDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC' AND @EndDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC'

The following query seems to work (2021-03-29):

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-29 00:00:00'
SET @EndDate = '2021-03-29 23:59:59'

SELECT
    ColDate AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time',
    ColName
FROM tblName
WHERE ColDate  BETWEEN @StartDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC' AND @EndDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC'

---

UPDATE - QUESTION:

The above update seems to work, but am I overlooking anything?

Answer 1

Your updated answer seems great. But if the correct entered date is always without time and only incorrect ones are entered with time part then you can also just subtract 1 hour from @startdate while using it in where clause.

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-29 00:00:00'
SET @EndDate = '2021-03-29 23:59:59'

SELECT *
FROM tblName
WHERE ColDate BETWEEN dateadd(day,-1,@StartDate) AND @EndDate

But it won't work if there are rows with time part other than day light saving issue.