SQL calculates the number of working days between two dates

Source: Internet
Author: User
If exists (select * from dbo. sysobjects where id = object_id (n' [tb_Holiday] ') and OBJECTPROPERTY (id, n'isusertable') = 1)
Drop table [tb_Holiday]
GO

-- Define holiday table
Create table tb_Holiday (
HDate smalldatetime primary key clustered, -- holiday period
Name nvarchar (50) not null) -- holiday Name
GO

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_WorkDay] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [f_WorkDay]
GO

-- Calculate the number of working days between two dates
Create function f_WorkDay
(@ Dt_begin datetime, -- start date of Calculation
@ Dt_end datetime -- end date of the calculation)
RETURNS int
AS
BEGIN
IF @ dt_begin> @ dt_end
RETURN (DATEDIFF (Day, @ dt_begin, @ dt_end) + 1-(select count (*) FROM tb_Holiday WHERE HDate BETWEEN @ dt_begin AND @ dt_end ))
RETURN (-(DATEDIFF (Day, @ dt_end, @ dt_begin) + 1-(select count (*) FROM tb_Holiday WHERE HDate BETWEEN @ dt_end AND @ dt_begin )))
END
GO

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_WorkDayADD] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [f_WorkDayADD]
GO

-- Add working days on the specified date
Create function f_WorkDayADD (
@ Date datetime, -- base date
@ Workday int -- the number of working days to be added)
RETURNS datetime
AS

BEGIN
IF @ workday> 0
WHILE @ workday> 0
SELECT @ date = @ date + @ workday, @ workday = count (*) FROM tb_Holiday WHERE HDate BETWEEN @ date AND @ date + @ workday
Else while @ workday <0
SELECT @ date = @ date + @ workday, @ workday =-count (*) FROM tb_Holiday WHERE HDate BETWEEN @ date AND @ date + @ workday
RETURN (@ date)
END

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.