Is it misleading that first_execution_time returns a +00:00 DATETIMEOFFSET?

[ReeceGoding]

Version of the script Pretty sure this has always been the case.

What is the current behavior? I spend most of my time in the GMT+0 timezone, which is the exact same as UTC. This means that if anyone is going to wrongly complain about a UTC conversion being confusing, then it's me. I'm geographically the least likely to benefit from UTC being done right. It's quite possible that I wrongly think that I understand UTC. I'm very open to being totally wrong on this. However, I'm pretty sure that first_execution_time and first_execution_time_utc are reporting times that don't agree.

If the current behavior is a bug, please provide the steps to reproduce. Run sp_QuickieStore. I'm currently on the GMT+1 timezone, so earlier today I was able to see first_execution_time report 2024-06-25 12:00:11.8400000 +00:00 whereas first_execution_time_utc reported 2024-06-25 11:00:11.8400000 +00:00 on the same row. I'm pretty sure that isn't how UTC offsets are supposed to work. Two equivalent times shouldn't have the same offset while showing different times. The other date columns that have a UTC version share this issue.

What is the expected behavior? Why not just make first_execution_time not be a DATETIMEOFFSET? If the final few characters will always be +00:00, then we can cut them.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures? Pretty sure this has always been the case.

IMPORTANT: If you're going to contribute code, please read the contributing guide first. https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md Will do, but see above about how I'm geographically the worst for the job.

[erikdarlingdata]

@ReeceGoding if you want to figure out what's going wrong and fix the actual issue, cool, but just changing a data type doesn't do that.

[ReeceGoding]

@erikdarlingdata I think I'm on to something. Try this with your own timezone. I think that mine (British Summer Time) corresponds to @timezone = 'GMT Standard Time' in sys.time_zone_info terms, but I haven't found a way to get SQL Server to confirm that to me.

SELECT
    SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET,
    CURRENT_TIMEZONE() AS CURRENT_TIMEZONE;

EXEC sp_quickiestore @timezone = 'GMT Standard Time',
                     @hide_help_table = 1,
                     @sort_order = 'recent';

EXEC sp_quickiestore @hide_help_table = 1, @sort_order = 'recent';

This screenshot shows that I get different results in the first_execution_time column, even though explicitly passing in my timezone to @timezone shouldn't change anything. If my understanding is correct, then the offsets shown in the output from passing in @timezone = 'GMT Standard Time' are the only correct ones.

So I suspect that all that we have to do to fix this is to make the default value of @timezone be the user's timezone rather than NULL. What do you think? The only problem is that I don't know how to programmatically grab the relevant entry from sys.time_zone_info.