Find all jobs scheduled between two dates

[Eamon]

Hi all, I'm trying to find a way to list all the jobs/schedules due to run between two dates but all I seem to be able to get is a list of jobs with their NextRunTime.

Example:
Say I had two jobs. The first job runs hourly on the hour and the second runs daily at 2pm.

Now if it was now (26 Jan 2007 11:20am) and I want to find out what jobs will run between 12pm and 4pm on 1st Feb 2007, I would want a list something like this:

JobID | RunTime
-----------------------------------
1 | 1 Jan 2007 12:00
1 | 1 Jan 2007 13:00
1 | 1 Jan 2007 14:00
2 | 1 Jan 2007 14:00
1 | 1 Jan 2007 15:00
1 | 1 Jan 2007 16:00

using enterprise manager I can only see the next run date so it would look like this:
JobID | RunTime
-----------------------------------
1 | 26 Jan 2007 12:00
2 | 26 Jan 2007 14:00

To me this seems like it would be something that would be really useful for a lot of people, especially DBAs needing to do server maintenance so I would be surprised if it hasn't been done before but I can't seem to a way anywhere. I don't really want to go to third party tools if I can avoid it, with the schedule definitions stored in the database it seems like it would be possible with a stored procedure that took two dates as parameters.

In the end I may need to create my own but as I said before it seems like such a useful SP that someone would have already done it and I'd rather not re-invent the wheel.

Regards,
Eamon

[iburyak]

Show your query, please.

[iburyak]

See my query:


[PHP]Select *
From (select name, next_run_date, next_run_time,
convert(datetime, convert(varchar(20), convert(datetime,convert(varchar(20),next_run_date )),101) + ' ' +
left(convert(varchar(20), next_run_time, 108),len(next_run_time)-4) + ':' + left(right(convert(varchar(20), next_run_time),4),2)) datetime
from msdb..sysjobschedules
where next_run_date > 0 and next_run_time > 0 ) a
where datetime between getdate() and dateadd(hh,4,getdate())[/PHP]

This query was challenging because date and time stored in different columns as integers.