Based on http://stackoverflow.com/a/252532/819417
ALTER FUNCTION dbo.fn_WorkDays(@StartDate DATETIME) RETURNS INT
AS
BEGIN
IF @StartDate IS NULL
RETURN NULL
--Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
--Usually faster than CONVERT.
--0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
DECLARE @EndDate DATETIME
SELECT @EndDate = dateadd(day, -1, dateadd(month, 1, @StartDate))
RETURN (SELECT --Start with total number of days including weekends
(DATEDIFF(dd, @StartDate, @EndDate)+1)
--Subtract 2 days for each full weekend
-(DATEDIFF(wk, @StartDate, @EndDate)*2)
-(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, @EndDate) = 7 THEN 1 ELSE 0 END))
END
No comments:
Post a Comment