I am writing a query that checks what time a person took breaks during a night shft. I have a "Breaks" table that sets the start and end time of the break. The break is between 22:00:00 PM and 03:00:00 AM. In my query, (I have inserted a part of it here as the query is rather large), I use the following code:
left outer JOIN Breaks ON Convert(varchar,ClockTime, 8) >= convert(varchar, Break_Start, 8) and ClockTime <= convert(varchar, Break_End, 8) and SRDay.SRDay_ID = SRBreak.SRDay_ID
The problem is that this would work fine for a day shift, but in the night shift, it checks if the ClockTime < Break_End, which, is for eg. 22:00:00, which is incorrect
Can anyone help. Please note. The reason why I take only the time portion from Break_Start and Break_End is that the system asks the user tp enter the time and the DB sets the date according to the date the user saved the entry. I need to change it such that the date is the date of the ClockTime, and the time is the time of the Break_Start and Break_End.
Can anyone help?