Select rows on time criteria

Hi,

I would like to retrieve only the rows after 23:59:59 but I am getting the output as shown in the picture.

Please advise how to write a correct where clause

SELECT ID, STARTDATE, STARTTIME, ENDTIME

FROM MACHINELOG

WHERE (STARTTIME > CAST('00:00:00' as time(0)) AND ENDTIME < CAST('09:00:00' as time))

ORDER BY ID DESC

TimeRows

hi

hope this helps

create sample data script

drop table if exists #MachineLog

create table #MachineLog (ID int , startDate date, startTime time , endTime time)
insert into #MachineLog select 287106,'2023-03-07','22:43:37.0000000','01:15:25.0000000'
insert into #MachineLog select 287105,'2023-03-08','00:38:09.0000000','00:54:17.0000000'
insert into #MachineLog select 287091,'2023-03-07','23:15:45.0000000','01:15:25.0000000'
insert into #MachineLog select 287092,'2023-03-08','00:18:09.0000000','02:35:09.0000000'

SELECT 
     ID, STARTDATE,  STARTTIME, ENDTIME
FROM 
   #MACHINELOG
WHERE 
   DateName("hh", STARTTIME) between 0 and 9
      AND 
   DateName("hh", ENDTIME) between 0 and 9
ORDER BY 
    ID DESC

So what you really want are those records where start time is between midnight and 9:00 AM AND endtime is between midnight and 9:00 AM. If so,

SELECT * 
FROM 
    MACHINELOG 
WHERE 
    startTime >= '00:00:00.0000000' AND startTime < '09:00:00.0000000'
    AND endTime >= '00:00:00.0000000' AND startTime < '09:00:00.0000000'

In your example on the first row, I am guessing that the end time is on the following day, i.e, 2023-03-08. So you could alternatively write your query to specify that starttime should be less than end time, like this:

SELECT * 
FROM 
    MACHINELOG 
WHERE 
    startTime >= '00:00:00.0000000'
    AND endTime  < '09:00:00.0000000'
    AND startTime < endTime;

BTW, What @harishgg1 posted should also work. Avoiding functions in where clause is a good practice for allowing query optimizer to use available indexes. For this reason, I prefer the queries I posted here.

1 Like

Hi

Function Based Indexes

Just a thought .. Please excuse

Your where clause says StartTime > 00:00:00 and ENDTIME < '09:00:00')

All of the EndTime values in your table are < 9:00:00.

Thank you so much to all the gurus who helped me to collect the task.
Please review to the screenshot of the completed task and give your valuable comment.