Many years ago I worked for an energy business. One nuisance we had to deal with was the “clock shift” when BST (British Summer Time) began or ended. The official prescription for the clock change in the United Kingdom is found at
https://www.gov.uk/when-do-the-clocks-change
“In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.” So the megawatt hours supplied in a contract starting outside BST and ending inside BST would be one hour less than expected, while a contract starting inside BST and ending outside BST would total one megawatt hour more than expected.

Since then I haven’t thought of it much but I was listening to a podcast where a DBA mentioned problems using Sql Server’s GETDATE() or SYSDATETIME() functions as default for a date/time column in a logging table. Where the start and end times of a process are logged,the duration of the process is calculated by subtracting the start time from the end time. Any calculations that cross the clock shift an odd number of times will be out by an hour one way or the other. He suggested using the SYSUTCDATETIME() function instead. UTC (co-ordinated universal time) has no clock shift so all time calculations are correct. That is a valid approach, and will be the only solution available if the column type can’t be changed from date or datetime.

This can be seen if we compare a clock-shifted time which has been cast to a datetime.
SELECT DATEDIFF(minute, SYSUTCDATETIME(), CAST(SWITCHOFFSET(SYSUTCDATETIME(), '-01:00') as datetime))

A difference appears because the datetime (and datetime2) types don’t incorporate any offset information. There is an alternative though, which is using a datetimeoffset data type. This can handle timezone offsets, as you can see by running the SWITCHOFFSET(), which returns a datetimeoffset:
SELECT SWITCHOFFSET(SYSDATETIME(), '-01:00')

Now we re-run the above comparison with “now” in UTC compared to “now” in a mythical timezone that’s 14 hours behind it.
SELECT DATEDIFF(minute, SYSUTCDATETIME(), SWITCHOFFSET(SYSDATETIME(), '-14:00'))

As you can see, there’s no difference. Datetimeoffset gives you local time when you need it and calculations are still based on UTC. Storage really shouldn’t be an issue here, but for what it’s worth the column length of “datetimeoffset” is 10 bytes, but you can cut it to 8 by defining it as “datatimeoffset(0)”.

Problems with clock shifting are not particularly common, but with this type of issue I prefer to pick up a good habit so that I’ll never have to deal with it. I’ve been using datetimeoffset for a while now, just as a good habit because applications are becoming more and more international. I can now see a benefit in using it for purely local applications.

NB: If you are considering using SYSUTCDATETIME() as a default for a datetime or datetime2 column, or redefining the same column as a datetimeoffset, consider whether you have a responsibility to maintain consistency between historical values and the ones that will be added after your change. You may not be allowed to update the existing values to bring them in line with the new ones, and it would probably be hard to do, especially if they go back a long way. One alternative might be to leave the existing column alone and add a new one. There probably is a limit to how long you have to keep the data, whether it’s a regulatory requirement or just relevance to the business. Once the prior values were all over the time horizon, you’d be able to drop the old column.