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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.