select SERVER, stop_execution_date from msdb..sysjobactivity inner join msdb..sysjobhistory on msdb..sysjobactivity.job_id = msdb..sysjobhistory.job_id where stop_execution_date > This is the query I am working with. stop_execution_date is "datetime" datatype. I need to get details of job which runs after certain time. How to do it?
Let's try this again. My first answer was incorrect because I misread the question. ;-) You'll probably want to look at the CAST/CONVERT functions. Here is an example of both: SELECT SERVER , CAST(stop_execution_date AS DATE) AS TheDate, CAST(stop_execution_date AS TIME) AS CastTheTime, CONVERT(TIME,stop_execution_date) AS ConvertTheTime, stop_execution_date FROM msdb..sysjobactivity INNER JOIN msdb..sysjobhistory ON msdb..sysjobactivity.job_id = msdb..sysjobhistory.job_id WHERE CAST(stop_execution_date AS DATE) >= '2014-07-14' AND CAST(stop_execution_date AS TIME) >= '05:00:00' I included the addition filter in the where clause to just narrow down the results on my test server and to confirm that the code was working as I expected. You could also just adjust the where clause in your original query to reflect the appropriate time: SELECT SERVER , stop_execution_date FROM msdb..sysjobactivity INNER JOIN msdb..sysjobhistory ON msdb..sysjobactivity.job_id = msdb..sysjobhistory.job_id WHERE stop_execution_date > '2014-07-14 14:00:02' Keeping in mind that if you want that time (2:00:02PM) to be included you'll need to use the '>=' operator. Hope this helps!