从 msdb.dbo.sysjobhistory 获取最近 24 小时的作业记录

问题描述

我想编写一个查询以从msdb.dbo.sysjobhistory"表中获取最近 24 小时的作业记录，但我无法获取，因为我得到run_date"和run_time"列返回为一个号码.如何将run_date"和run_time"列转换为日期时间变量，并使用它来获取过去 24 小时的工作历史记录?

I want write a query to get the last 24 hours worth of job record from the "msdb.dbo.sysjobhistory" table, but I can't get because I get the "run_date" and "run_time" columns are returned as a number. How can I convert the "run_date" and "run_time" columns into a datetime variable, and use this to get the last 24 hour job history?

推荐答案

查看 这篇文章 - 它展示了如何从 sysjobhistoryrun_date 列/代码>.

Check out this post - it shows how to "decode" those run_date columns from sysjobhistory.

您应该能够通过这样的查询获取过去 24 小时内的条目:

You should be able to get the entries from the last 24 hours with a query something like this:

SELECT 
    j.name as JobName, 
    LastRunDateTime = 
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
FROM 
    msdb..sysjobs j
INNER JOIN
    msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(HOUR, -24, GETDATE())

这篇关于从 msdb.dbo.sysjobhistory 获取最近 24 小时的作业记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！