I agree. I use to have date and time as separate columns but now I have switched to DATETIME and avoid all the problems associated with elapsed times that span more than one day. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: Friday, March 04, 2016 1:46 PM To: [email protected] Subject: [RBASE-L] - Re: Midnight shift notes I like Albert's better. Bruce ------ Original Message ------ Sent: 3/4/2016 11:40:56 AM Subject: [RBASE-L] - Re: Midnight shift notes From: "Albert" <[email protected]> To: [email protected] Cc: For your purposes, you could calculate the full DATETIME value of the shift start and end, and then a simple (EndDateTime-StartDateTime) = Elapsed will give you the elapsed time. Said time will probably be in seconds, so minor adjustments would be necessary. Albert On 2016-03-04 12:11 PM, [email protected] wrote: I've never had to do this. Would it help if you had 2 computed date columns that had a DEXTRACT of the start and end? I'm assuming you have 2 DateTime columns now? Because then if the 2 DEXTRACT columns are the same then you can simply do math of the time; if not then you add together 2 times, from beginning to midnight and then from midnight to end.
Can't think of anything more elegant... Karen -----Original Message----- From: Tom Frederick mailto:[email protected] To: karentellef mailto:[email protected] Sent: Fri, Mar 4, 2016 12:35 pm Subject: [RBASE-L] - Midnight shift notes We have to track activity by shift and write a note for record purposes. All notes have a staff ID, topic, actual note, date and start/end times which provides proof of activity. Right now I can bill activity by a whole day, but a change to billing by the hour is coming. For day and evening shifts it is pretty easy. They stay same day and within a consecutive time frame. Overnight shifts have to deal with Midnight date/time changes to automatically split over two dates and make 24 hour clock time adjustments. I can check for overlap or bad start/stop times within the same date. Making adjustments for Midnight date/time is just not clicking in my head. Any suggestions are welcome. Tom Frederick President/CEO Elm City Center 1314 W Walnut Jacksonville, IL 62650 O-217-245-9504 F-217-245-2350 [email protected]<mailto:[email protected]>
