Count Working Days

Sometimes people need to count the difference between two dates. In Sql Server 2000 and 2005 it’s not a problem. To count the difference between the days you van use the following statement:

DATEDIFF(hh, Date 1, Date 2, ) / 24

The difficulty comes when you want to count only the working days of the dates. Maybe not many developers have this problem… but i couldn’t find a solution. After searching i found this function…


CREATE FUNCTION dbo.GetWorkingDays
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @range INT;
SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;RETURN
(
SELECT
@range / 7 * 5 + @range % 7 -
(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= @range % 7
AND DATENAME(WEEKDAY, @endDate - d + 1)
IN
(
'Saturday',
'Sunday'
)
)
);
END
GOYou have to create a new function with the previous coding. I tried it but an error kept loading saying that "Incorrect syntax near 'FUNCTION'". So again i began my search which led me to another website with the solution. The problem was that to use the functions in sql server, the compatibility level must be set to 80 as functions where being used in sql server 2000. To check the compatibility level of your database, you must run the query analyser and enter the following statement:


sp_dbcmptlevel DatabaseName If it returns 70, then the compatibility level is the problem. All you need to do is to run the next statement (always in the query analyser):sp_dbcmptlevel DatabaseName, 80

And voila, the function can now be saved. If that didn't solve the problem... then you'd better keep on searching! To return to compatibility 70 you must run the previous statement and instead of 80, enter 70:sp_dbcmptlevel DatabaseName, 70 Happy Coding!!! :)

Hope i helped someone today! Any comments?
Next are the links from where i got the information:

link 1 – aspfaq
link 2 – sqlteam
link 3 – gmbsupport