Copy Code code 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
--Calculates the number of working days that differ by two dates
CREATE FUNCTION F_workday (
@dt_begin datetime,--the calculated start date
@dt_end datetime--The calculated end date
) 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 (' 2009-10-10 ', ' 2009-10-1 ')
/*
-----------
7
(1 data columns are impacted)
*/
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
--Calculates the number of working days that differ by two dates
CREATE FUNCTION F_workday (
@dt_begin datetime,--the calculated start date
@dt_end datetime--The calculated end date
) 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 (' 2009-10-10 ', ' 2009-10-1 ')
/*
-----------
-7
*/
