-1

We are running SQL Server 2008 R2, so for Timezone stuff we use Matt Johnson's SqlServerTimeZoneSupport/ We convert datetimes on the fly using ConvertZone for our clients. Some Australian states support Daylight Savings, some don't. Upgrading SQL Server is not possible.

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, Tzdb.ConvertZone([start], 'Australia/NSW', 'Australia/Queensland', 1, 1)) as StartAdjusted,
    [End],
    CONVERT(datetime, Tzdb.ConvertZone([end], 'Australia/NSW', 'Australia/Queensland', 1, 1)) as EndAdjusted
FROM @dates

Tzdb.ConvertZone() is installed from the SQLServerTimeZoneSupport git linked above. Its created as it's own TZDB schema.

All of these adjusted dates return NULL. Any advice?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Hecatonchires
  • 1,009
  • 4
  • 13
  • 42
  • [The author states](https://stackoverflow.com/a/65694155/1127428) "Alternatively, if for some reason you must do this entirely in SQL Server without converting to Windows time zones, then you will need to rely on projects such as my SqlServerTimeZoneSupport project. That one is a bit old and not very well maintained, so I recommend against that approach if you can help it." – Dale K Apr 12 '21 at 03:07
  • 1
    I’m voting to close this question because its a question intended for the support team of the component. – Dale K Apr 12 '21 at 03:07
  • You could trying pinging the author by adding a comment to his answer above... – Dale K Apr 12 '21 at 03:13
  • I was actually hoping for other users with timezone knowledge to comment. I have created a ticket on git for the author. – Hecatonchires Apr 12 '21 at 04:42
  • @matt-johnson-pint are you there? https://stackoverflow.com/users/634824/matt-johnson-pint – Hecatonchires Apr 12 '21 at 04:47
  • Yes, but I'm a bit tied up at the moment. Will investigate when I have availability. – Matt Johnson-Pint Apr 12 '21 at 16:30
  • Ty Matt. Dirty fix at the moment is unadjusted date is displayed :( – Hecatonchires Apr 13 '21 at 03:21

1 Answers1

0

@matt-johnson-pint suggested I refresh my Timezone tables. This involved downloading and using the SqlTzLoader from the Project page, running for each server (set the correct connection string). I was confused, as it outputs no text if successful, but it fixed the issue. My test case is now fine.

Hecatonchires
  • 1,009
  • 4
  • 13
  • 42