Function to calc working days between 2 dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Function to calc working days between 2 dates

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-01-19 : 10:14:18

Not massively complicated to be honest, but here you go. Note: it was for calculating work absences and the END date was the date of return to work. So adjust it if you like for you own needs.

CREATE FUNCTION FNCalcWorkingDays (
@Start datetime,
@End datetime)

RETURNS int
AS
BEGIN
-- declare vegetables
DECLARE @counter datetime
SET @counter = @Start
DECLARE @WorkingDays int
SET @WorkingDays = 0

WHILE @counter < @End
BEGIN
-- if date is mon-fri, increment @workingDays, else leave
IF DATEPART(dw,@Counter) in (2,3,4,5,6)
SET @WorkingDays = @WorkingDays +1
ELSE
SET @WorkingDays = @WorkingDays
SET @counter = DATEADD(dd,1,@counter)
END
-- return result
RETURN @WorkingDaysOff
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 10:43:42

one problem with above is it assumes that DATEFIRST setting is always 7

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-19 : 11:02:29

Your code will not work correctly if the setting of DATEFIRST <> 7, the default for US english.

set datefirst 7
-- For Monday, 2009-01-19
select DW1 = DATEPART(dw,'20090119')
set datefirst 2
select DW2 = DATEPART(dw,'20090119')

Results:
DW1         
----------- 
2

(1 row(s) affected)

DW2         
----------- 
7

(1 row(s) affected)

It would not have this problem if you replaced this code:

IF DATEPART(dw,@Counter) in (2,3,4,5,6)

with this code, because it returns the same result with any setting of DATEFIRST:

IF datediff(dd,'17530101',getdate())%7 in (0,1,2,3,4)

Also, there seems to be no point to this code:

SET @WorkingDays = @WorkingDays

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 15:11:35

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107799
(has no loop, just simple calculation)

quote:

create function workhours_jeff(@StartDate datetime, @EndDate datetime) returns float as
begin

return CAST(
                (
                  (DATEDIFF(dd,@StartDate,@EndDate)+1)
                 -(DATEDIFF(wk,@StartDate,@EndDate)*2) 
                 -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
                 -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
                 )
                -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' or DATENAME(dw,@StartDate) = 'Saturday' THEN 0
                       ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
                -(CASE WHEN DATENAME(dw,@EndDate) = 'Sunday' or DATENAME(dw,@EndDate) = 'Saturday' THEN 0 
                       ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
        AS FLOAT) * 24
end

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources