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
hi
hope this helps
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.
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.
