SQL - How to set a specific time to Datetime

NerishaB
NerishaB used Ask the Experts™
on
Hi,

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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul MacDonaldDirector, Information Systems

Commented:
Rather than just compare time, why not compare datetime?  

Author

Commented:
I cant compare datetime, becasue the system only requests that the user enter the TIME of the Break start and Break end.  So I could get this:

ClockTime = 2011/01/05 22:30:00
BreakStart = 2012/05/01 22:00:00
BreakEnd = 2012/05/01 01:00:00

Do you understand what my problem is?
Paul MacDonaldDirector, Information Systems

Commented:
No.  When you capture the time, can't you also capture the date?  Or is there a scenario where the user is entering a break time for a date in the past or a date in the future?  

I had imagined the events would be entered as they happened, like a timeclock, but I may be wrong about that.  In your example, where are the dates coming from if the user is only entering times?

Author

Commented:
The user enters the break time that is in the past, I cannot change that.
Paul MacDonaldDirector, Information Systems

Commented:
How about this:
CAST both StartTime and EndTime as datetimes.
If EndTime is less than StartTime, DATEADD 24 hours to it.
Do your math (DATEDIFF).
Return the result CAST as a time.

Author

Commented:
Can you give me an example of how it is done?
Director, Information Systems
Commented:
Not knowing anything about the rest of your query or the layout of your tables, you can start with this and change it as you need to...

LEFT OUTER JOIN Breaks ON
  CONVERT(DateTime, ClockTime) >= CONVERT(DateTime, Break_Start)  
  AND
  CONVERT(DateTime, ClockTime) <= CONVERT(DateTime, Break_End)  
  AND
  SRDay.SRDay_ID = SRBreak.SRDay_ID

LEFT OUTER JOIN Breaks ON
  CONVERT(DateTime, ClockTime) >= DATEADD(d, 1, CONVERT(DateTime, Break_Start))
  AND
  CONVERT(DateTime, ClockTime) <= DATEADD(d, 1, CONVERT(DateTime, Break_End))
  AND
  SRDay.SRDay_ID = SRBreak.SRDay_ID

...the first join is the same as what you have now.  The second join should grab data from entries where the break time is otherwise outside of the work time.  

I have a feeling your method will fail you at some point so I encourage you to try to grab/store the date somehow at some point.  Otherwise...best of luck!

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial