Network days - SQL Server Forums

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

New to SQL Server Programming

Network days

Author

Topic

jjz
Starting Member

31 Posts

Posted - 2012-02-22 : 02:07:08

good Day everyone

I need your help.

I am trying to calculate the trading days, excluding public holidays and weekends and I came through this function.

however it returns a 0. May you kindly assist.

ALTER FUNCTION [dbo].days_diff ( @STARTDATE datetime,@ENDDATE datetime )
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(d,@STARTDATE,@ENDDATE)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@STARTDATE))
not in('Saturday','Sunday')
set @i=@i+1
End

RETURN @count

END

Select dbo.days_diff(01/02/2012,22/02/2012) as diff

anumodhc
Starting Member

13 Posts

Posted - 2012-02-22 : 04:22:25

DECLARE @StartDate datetime,
@EndDate datetime
SELECT @StartDate = '01/01/2012',@EndDate = '01/31/2012'
SELECT COUNT(dys) FROM
(SELECT DATEADD(DAY, v.Number, @StartDate ) AS dys
FROM master..spt_values AS v
WHERE v.Type = 'P'
) result WHERE dys <= @EndDate AND DATEPART(DW,dys) NOT IN (1,7)

This will exclude Saturday and Sundays.

AnumodH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources