SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Function to calc working days between 2 dates		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theboyholty
Posting Yak Master

United Kingdom
221 Posts
Posted - 01/19/2009 :  10:14:18  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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

India
48076 Posts
Posted - 01/19/2009 :  10:43:42  Show Profile  Reply with Quote
one problem with above is it assumes that DATEFIRST setting is always 7
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7007 Posts
Posted - 01/19/2009 :  11:02:29  Show Profile  Reply with Quote
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
Edited by - Michael Valentine Jones on 01/19/2009 11:04:00
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 01/19/2009 :  15:11:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Edited by - SwePeso on 01/19/2009 15:12:39
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000