CREATE FUNCTION dbo.CalcWorkHours (
@start_date DATETIME,
@end_date DATETIME)
RETURNS INT
AS
BEGIN
RETURN
(SELECT ((total_days / 7) * 5 + total_days % 7 -
CASE WHEN 6 BETWEEN start_weekday AND end_weekday
THEN 1 ELSE 0 END -
CASE WHEN 7 BETWEEN start_weekday AND end_weekday
THEN 1 ELSE 0 END) * 8
FROM (SELECT total_days, start_weekday,
start_weekday + total_days % 7 - 1
FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,
DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)
) AS T(total_days, start_weekday)
) AS D(total_days, start_weekday, end_weekday)
);
END
GO
SELECT dbo.CalcWorkHours('20080401', '20080421')
/*
-- Result
120
*/
Labels: t-sql programming