Date Listing - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date Listing

Author

Topic

erica686
Starting Member

13 Posts

Posted - 2010-02-08 : 00:25:47

I need to count all working days within a specified period. I thought I could maybe list all dates within the period, and then count the number of days between 2 and 6. I am having difficulty listing all dates within the period - any help?!?

Public holidays do not matter, as these are recorded in our system.

Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-08 : 00:43:49

Post your table structure and expected output

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-08 : 02:01:53

Do you have a calendar table ?

You can use this F_TABLE_DATE if you don't have one.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 04:51:39

you can use a function like below


CREATE FUNCTION dbo.DayCount
(
@StartDate datetime,
@EndDate datetime,
@WorkingDaysOnly bit
)
RETURNS int 
AS
BEGIN
DECLARE @DayCount int

;With Calendar(Date,Day)
AS
(SELECT @StartDate,DATENAME(dw,@StartDate)
UNION ALL
SELECT DATEADD(dd,1,Date), DATENAME(dw,DATEADD(dd,1,Date)
WHERE DATEADD(dd,1,Date) <= @EndDate
)

SELECT @DayCount=COUNT(*)
FROM Calendar
WHERE @WorkingDaysOnly=0
OR Day NOT IN ('Saturday','Sunday')
RETURN @DayCount
END

then call like SELECT dbo.dbo.DayCount(your start date,your end date,1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources