The SQL statement is used to calculate the number of working days between two dates.

The SQL statement is used to calculate the number of working days between two dates.

/*
Due to a strange demand at work, the SQL language is used to calculate the number of working days between two days.
It is set to have 5 working days in a week, from Monday to Friday.
Note:
Number of working days in the first week: DATEPART (dw, @ begdt)-DATEPART (dw, @ begdt), minimum 0 days
Number of working days in the last week: DATEPART (dw, @ enddt), up to 5 days
Calculation method:
If the two dates are within the same week, calculate the number of working days in the first week"
Otherwise, the calculation is as follows:
(The total number of days in the two-day period-the number of days in the first week-the number of days in the last week)/7*5
+ Number of working days in the first week
+ Number of working days in the last week
*/

-- Calculate and return the number of working hours (working day * 8) between two days. Calculated from Monday to Friday. -- set datefirst 1 create function dbo first. calcWorkHours (@ bdate DATETIME, @ edate DATETIME) returns integeras begin declare @ hours integer if @ DATEFIRST <> 1 OR @ bdate> @ edate RETURN-1 SELECT @ hours = -- IF the end date and start date are within the same week, it only takes a few days to calculate the case when datepart (wk, @ edate-1)-DATEPART (wk, @ bdate) = 0 then case when datepart (dw, @ bdate)> 5 THEN 0 when datepart (dw, @ edate-1)> 5 THEN 6-DATEPART (dw, @ bdate) else datepart (dw, @ edate-1)-DATEPART (dw, @ bdate) + 1 END -- if the END date and start date are in different weeks, calculate the complete number of weeks x 5 ELSE (DATEDIFF (dd, @ bdate, @ edate)-(8-DATEPART (dw, @ bdate)-DATEPART (dw, @ edate-1 )) /7*5 -- plus the number of working days in the first week + case when datepart (dw, @ bdate) <6 THEN 6-DATEPART (dw, @ bdate) ELSE 0 END -- plus the number of working days in the last week + case when datepart (dw, @ edate-1)> 5 THEN 5 else datepart (dw, @ edate-1) END * 8 RETURN @ hoursEND

The above is all the content of this article, hoping to help you learn.