'Need to get the deadlock logs in SQL Server for yesterday

I am trying to use the query below for getting the xml report for deadlocks for the current day.

DECLARE @xelfilepath NVARCHAR(260)

SELECT @xelfilepath = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;

SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'

DROP TABLE IF EXISTS  #TempTable

SELECT CONVERT(XML, event_data) AS EventData
INTO #TempTable 
FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'

SELECT 
    EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime, 
    CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, 
    EventData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime, 
    EventData.query('event/data/value/deadlock') AS XmlDeadlockReport
FROM 
    #TempTable
ORDER BY 
    UtcTime DESC;

I need to get the logs for yesterday, need help in updating this query to get the logs a day before.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source