Anyone who has worked with SharePoint *should* be familiar with the ULS log where SharePoint can log nearly every operation. Most recent versions allow for a portion of those log files to be cataloged into a database called ULSTraceLog that makes searching easier.
SharePoint logs the records based on UTC time.
However, sometimes, you need to find the exact log file on the SharePoint server based on an event in the database. Depending on how your log files roll over, this might be a challenge.
Below is the work in progress query to convert the UTC time to local time and then further convert to 12 hour time. It is written as a CTE so I could get more familiar with that and it seems to work fine but I don’t know the performance around two CTE’s:
SELECT TOP 100
,CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [ULSTraceLog].LogTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LogTimeLocal
ORDER by LogTime DESC
,substring(convert(varchar(max), LogTimeLocal,20),0,12) + substring(convert(varchar(20), [LogTimeLocal], 9), 13, 5) + ‘ ‘ + substring(convert(varchar(30), [LogTimeLocal], 9), 25, 2) as LogTimeLocal12Hour from CTE_UlsLogFile
select * from CTE_ULSLogFile_InLocal
Comments or thoughts please!