Results 1 to 5 of 5

Thread: DATEDIFF for business days only

  1. #1
    Join Date
    Aug 2007
    Posts
    2

    DATEDIFF for business days only

    I'm looking for a DATEDIFF function which will give me the
    BUSINESS days difference between 2 dates.
    e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4.
    is there such a function?
    Thx

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    No such builtin function in sql server.

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    Thx. any suggestions?

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    I wrote this a long time ago, but I haven't tested it thoroughly. I know it works for most situations, but I don't know about all. Try it and see if it works for you:


    declare @startdate datetime,
    @enddate datetime

    set @startdate = convert(datetime, '20070808')
    set @enddate = convert(datetime, '20070814')

    SELECT
    DATEDIFF( d,
    DATEADD( d,
    CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END,
    @startdate
    ),
    DATEADD( d,
    CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END,
    @enddate
    )
    )
    - DATEDIFF( wk,
    DATEADD( d,
    CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END,
    @startdate
    ),
    DATEADD( d,
    CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END,
    @enddate
    )
    ) * 2

  5. #5
    Join Date
    Aug 2007
    Posts
    3
    We had a similar problem. You could build a nonBizDays table with only non-business days in it. This table could be used in date queries to determine dates. Google will reveal some queries to load this table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •