search

mattjohnsonpint / SqlServerTimeZoneSupport

IANA Time Zone Support for Microsoft SQL Server
MIT License
193 stars 45 forks source link

ConvertZone suddenly returning NULL #39

Closed lukemason76 closed 3 years ago

lukemason76 commented 3 years ago

We convert datetimes on the fly using ConvertZone for our clients. Some Australian states support Daylight Savings, some don't.

I'm trying to solves an issue where ConvertZone is returning null for dates in the last week. Probably related to daylight savings. I've tried fiddling with the spring forward and fall back bits to no success.

Listed below is a test case:

`DECLARE @dates table ( [Start] datetime, [End] datetime )

insert into @dates([Start], [End]) VALUES ('2021-04-05 06:51:06.000', '2021-04-05 06:51:06.000'), ('2021-04-05 18:47:00.000', '2021-04-05 18:47:00.000'), ('2021-04-05 19:45:24.000', '2021-04-05 20:12:00.000'), ('2021-04-05 05:29:00.000', '2021-04-05 05:29:06.000'), ('2021-04-05 11:23:32.000', '2021-04-05 11:23:32.000'), ('2021-04-05 17:23:56.000', '2021-04-05 17:23:56.000'), ('2021-04-05 06:34:56.000', '2021-04-05 06:39:30.000'), ('2021-04-05 06:51:35.000', '2021-04-05 06:54:30.000'), ('2021-04-05 08:05:48.000', '2021-04-05 08:07:30.000'), ('2021-04-05 08:34:09.000', '2021-04-05 08:34:30.000'), ('2021-04-05 10:42:13.000', '2021-04-05 10:46:00.000'), ('2021-04-11 14:24:58.000', '2021-04-11 14:30:30.000'), ('2021-04-11 17:11:26.000', '2021-04-11 17:19:39.000'), ('2021-04-11 06:55:17.000', '2021-04-11 06:55:30.000'), ('2021-04-11 18:25:19.000', '2021-04-11 18:25:37.000'), ('2021-04-11 18:27:21.000', '2021-04-11 18:27:30.000'), ('2021-04-11 18:28:23.000', '2021-04-11 18:28:30.000'), ('2021-04-11 18:28:48.000', '2021-04-11 18:30:43.000')

SELECT [Start], CONVERT(datetime, Ctrack6_Custom.Tzdb.ConvertZone([start], 'Australia/NSW', 'Australia/Queensland', 1, 1)) as StartAdjusted, [End], CONVERT(datetime, Ctrack6_Custom.Tzdb.ConvertZone([end], 'Australia/NSW', 'Australia/Queensland', 1, 1)) as EndAdjusted FROM @dates`

All of these dates return NULL. Any advice?

mattjohnsonpint commented 3 years ago

Hi, I'm tied up at the moment but will investigate when I have availability and get back with you. In the meantime, you might want to make sure you've regenerated the time zone data tables recently.

lukemason76 commented 3 years ago

I had not 😢

After refreshing the data tables the test case works as expected 😄 Now it's off to revert those sp's