MSSQL calculates the code for two working days with different dates. For more information, see.
MSSQL calculates the code for two working days with different dates. For more information, see.
The code is as follows:
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 working days of two different dates
Create function f_WorkDay (
@ Dt_begin datetime, -- calculation start date
@ Dt_end datetime -- end date of calculation
) RETURNS int
AS
BEGIN
Declare @ I int
Select @ I = abs (datediff (dd, @ dt_begin, @ dt_end ))
Declare @ t table (dt datetime)
If @ dt_begin> @ dt_end
Insert @ t select dateadd (dd, number, @ dt_end) from master... spt_values
Where number <= @ I and type = 'p'
Else
Insert @ t select dateadd (dd, number, @ dt_begin) from master .. spt_values
Where number <= @ I and type = 'p'
Return (select count (*) from @ t where (datepart (weekday, dt) + @ datefirst-1) % 7 between 1 and 5)
END
GO
Select dbo. f_WorkDay ('2017-10-10 ', '2017-10-1 ')
/*
-----------
7
(A data column is affected)
*/
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 working days of two different dates
Create function f_WorkDay (
@ Dt_begin datetime, -- calculation start date
@ Dt_end datetime -- end date of calculation
) RETURNS int
AS
BEGIN
DECLARE @ workday int, @ I int, @ bz bit, @ dt datetime
Set @ workday = 0
IF @ dt_begin> @ dt_end
SELECT @ bz = 1, @ dt = @ dt_begin, @ dt_begin = @ dt_end, @ dt_end = @ dt
ELSE
SET @ bz = 0
WHILE @ dt_begin <= @ dt_end
BEGIN
SELECT @ workday = CASE
WHEN (@ DATEFIRST + DATEPART (Weekday, @ dt_begin)-1) % 7 BETWEEN 1 AND 5
THEN @ workday + 1 ELSE @ workday END,
@ Dt_begin = @ dt_begin + 1
END
RETURN (case when @ bz = 1 THEN-@ workday ELSE @ workday END)
END
GO
Select dbo. f_WorkDay ('2017-10-10 ', '2017-10-1 ')
/*
-----------
-7
*/
