sql function to calculate work hours
-
if the shift time is 7pm to 3am..after 12am,it will be considered as next day..and the time after 12am will be calculated as work hour of next day..please help me to write a sql function for this..pls...
-
if the shift time is 7pm to 3am..after 12am,it will be considered as next day..and the time after 12am will be calculated as work hour of next day..please help me to write a sql function for this..pls...
-
if the shift time is 7pm to 3am..after 12am,it will be considered as next day..and the time after 12am will be calculated as work hour of next day..please help me to write a sql function for this..pls...
Things you may want to consider when designing a system which includes a start time and end time: 1) Store both the date and time. This will make it much easier to determine if a shift crosses a midnight boundary. 2) Use the concept of a "End of Day" time. For example, lots of manufacturing sites use 11pm (23:00) because they consider anyone working after 11pm to be working the next day. 3) Also take into consideration a "Pay Date", typically this is what is considered the day the person works (and gets paid for). For example if your worker starts his shift on Friday at 7pm and works into Saturday morning, he would be paid for working Friday. Not 2 different pay days. (Fri & Sat) Best of luck.
-
Things you may want to consider when designing a system which includes a start time and end time: 1) Store both the date and time. This will make it much easier to determine if a shift crosses a midnight boundary. 2) Use the concept of a "End of Day" time. For example, lots of manufacturing sites use 11pm (23:00) because they consider anyone working after 11pm to be working the next day. 3) Also take into consideration a "Pay Date", typically this is what is considered the day the person works (and gets paid for). For example if your worker starts his shift on Friday at 7pm and works into Saturday morning, he would be paid for working Friday. Not 2 different pay days. (Fri & Sat) Best of luck.
Time can be a real nightmare to handle. Other issues which you might want to think about include: Day-light saving time (esp. around midnight boundaries). Time zones -- don't look applicable in your case, but I worked on a car hire problem which supported pickup/drop off across time zones. Of course, I've not actually answered the original question. Some idea of the data structure/data available might be useful.