Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.