How to calculate working days in SQL
How to calculate the working day in SQL, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.
CREATE FUNCTION Time_Span_of_minutes (
Start_day DATETIME
End_day DATETIME)
RETURNS FLOAT
BEGIN
-- return to calculate the interval between two periods of time by minutes, using the method of approaching day by day, calculating a day according to 8 hours and 480 minutes, excluding weekends, regardless of statutory holidays.
If the start date is on the weekend, the time of the day is calculated.
DECLARE minutes FLOAT
DECLARE next_day DATETIME
SET minutes=0.0
SET next_day=start_day
IF (TIMESTAMPDIFF (DAY,start_day,end_day) = 1.0 DO
IF ((DAYOFWEEK (next_day) = 7) OR (DAYOFWEEK (next_day) = 1)) THEN
SET next_day=next_day+INTERVAL 1 DAY
ELSE
SET next_day=next_day+INTERVAL 1 DAY
SET minutes=minutes+480.0
END IF
END WHILE
SET minutes=minutes+TIMESTAMPDIFF (MINUTE,next_day,end_day)
IF ((DAYOFWEEK (start_day) = 7) OR (DAYOFWEEK (start_day) = 1)) THEN
SET minutes=minutes+TIMESTAMPDIFF (MINUTE,start_day,CONVERT (CONCAT (SUBSTRING (start_day FROM 1 FOR 10),'17 CONCAT 30 FOR 00'), DATETIME))
END IF
END IF
RETURN minutes
END
CREATE FUNCTION Time_Span_of_minutes (
Start_day DATETIME
End_day DATETIME)
RETURNS FLOAT
BEGIN
-- return to calculate the interval between two periods of time by minutes, using the method of approaching day by day, calculating a day according to 8 hours and 480 minutes, excluding weekends, regardless of statutory holidays.
If the start date is on the weekend, the time of the day is calculated.
DECLARE minutes FLOAT
DECLARE next_day DATETIME
SET minutes=0.0
SET next_day=start_day
IF (TIMESTAMPDIFF (DAY,start_day,end_day) = 1.0 DO
IF ((DAYOFWEEK (next_day) = 7) OR (DAYOFWEEK (next_day) = 1)) THEN
SET next_day=next_day+INTERVAL 1 DAY
ELSE
SET next_day=next_day+INTERVAL 1 DAY
SET minutes=minutes+480.0
END IF
END WHILE
SET minutes=minutes+TIMESTAMPDIFF (MINUTE,next_day,end_day)
IF ((DAYOFWEEK (start_day) = 7) OR (DAYOFWEEK (start_day) = 1)) THEN
SET minutes=minutes+TIMESTAMPDIFF (MINUTE,start_day,CONVERT (CONCAT (SUBSTRING (start_day FROM 1 FOR 10),'17 CONCAT 30 FOR 00'), DATETIME))
END IF
END IF
RETURN minutes
END
This is the answer to the question about how to calculate the working day in SQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.
