2014-02-28

T-SQL: Work Days per Month

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