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
-
according to your where clause the output is right - so maybe you only interested on the rows where both start and end time are between your filter?
something like (starttime > xxx and starttime < yyy) AND (endtime > xxx and endtime < yyy)
-
you are validating two unrelated columns, right?
you need to correlate starttime and endtime
e.g. by adding ' and STARTTIME < ENDTIME '
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
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.
-
You have a start date and start time, and just and end time with no end date.
I’ll make a couple assumptions - that any shift completes what it starts, and nothing lasts more than 24 hours.
If end time < start time, it crossed midnight. For those records you need to capture the the time before midnight as part of the duration.
The data type for start time and end time, which you are casting as time, play a part in how to solve this. DateTime data type comes to mind as a way to calculate duration in a more simplified manner.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply