SQL DateTime Interval

[tracydirect]

Each record in my table logs when a process has started (starttime) and finished (endtime). I need to run a query to find out which processes run anytime during the hours of 08:00:00 and 17:00:00.

I can' figure out how to do this. Can someone please help.
I am using SQL Query analyzer to do this.

Thanks!!!

 

[PruSQLer]

I'm assuming you mean BETWEEN the hours of 8:00:00 and 17:00:00.

If you have the Between function, you can say

Where starttime between '8:00:00' and '17:00:00'

If you don't have the between function:

Where starttime > '8:00:00' and starttime < '17:00:00'

If you need to know that the job started AND finished between those times, you would add:

and EndTime < '17:00:00'

 

[tracydirect]

Will this work if the starttime is of the datetime data type or do I have to parse out the time part first? (ie. 2000-01-01 08:00:00)

 

[PruSQLer]

If starttime is a datetime data type, I would do

Where time(starttime) > '8:00:00' and time(starttime) < '17:00:00'

This would work in DB2. I don't know what your RDB uses so check the help info.