search

BrentOzarULTD / SQL-Server-First-Responder-Kit

sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.
http://FirstResponderKit.org
Other
3.37k stars 997 forks source link

sp_BlitzFirst - CheckDate is using UTC without timezone #735

Closed escharhon closed 7 years ago

escharhon commented 7 years ago

Do you want to request a feature or report a bug? Bug

What is the current behavior? When saving the output of sp_BlitzFirst to a table the CheckDate column is using datetimeoffset but it is storing it as UTC with no timezone information (00:00)

What is the expected behavior? The CheckDate (datetimeoffset) column should contain time zone info.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures? I have this running on SQL Server 2014 SP2 CU2 Developer and Enterprise editions. I am not aware if it was working in previous versions of the procedures.

BrentOzar commented 7 years ago

Found & fixed. The inserts all have to be done as dynamic SQL, so we were using this for the CheckDate field:

(CONVERT(NVARCHAR(100), @StartSampleTime, 127))

Unfortunately, style 127 strips out time zone offsets. Switched it to format 121 instead. To see the difference in action:

SELECT SYSDATETIMEOFFSET(), CONVERT(NVARCHAR(100), SYSDATETIMEOFFSET(), 127), CONVERT(NVARCHAR(100), SYSDATETIMEOFFSET(), 121)

To test, run the stored procs:

sp_BlitzFirst @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults_20170302'

And then check the history table:

SELECT * FROM DBAtools.dbo.BlitzFirstResults_20170302

It now works the same way sp_Blitz does.