Date Calculation Excluding Weekend & Holiday

  • I need to be able to find out the number of days between two date excluding weekend and holidays. For example:

    Datediff(day,creation_date,getdate())as Outstanding

    I want to find the number of days excluding holidays and weekend. Your help/suggestion is greatly appreciated.

    Thanks

  • To exclude weekends, there is a 'simple' solution doing some simple 'date'math. This has been solved before on these forums...

    To also exclude holidays is a lot trickier. The only 'easy' solution I can see is to build a table that holds all dates that should be excluded.

    The statement would be something like

    
    
    CREATE TABLE NonBusinessDays
    (NBDDate smalldatetime)
    GO

    SELECT datediff(day, @creation_date, getdate()) - count(*) AS Outstanding
    FROM NonBusinessDays
    WHERE NBDDate >= @creation_date and NBDDate < getdate()

    Either join to your source table or pass in Creation_date as a parameter.

  • NPeeters,

    Can you please elaborate on what is going on in the sample code you post. Especially the part - count(*). What purpose is this serving.

    This is how it was done in Access. However, I don't think there is a similar function in T-SQL.

    Days in Error: workdays([creation_date],Date())

    Thanks

  • Here's a user defined function that does what you need. You will have to create tblHolidays and populate it with all the holidays you want excluded.

    CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)

    RETURNS int

    AS

    BEGIN

    /*

    Description:

    Function designed to calculate the number of business days (In hours)

    between two dates.

    */

    DECLARE

    @Days int

    ,@WeekDays int

    ,@Holidays int

    ,@Hours int

    SELECT @Hours = DATEDIFF(Hour,@Start,@End)

    WHILE (DATEPART(WeekDay,@Start)-1) % 6 = 0

    BEGIN

    SELECT @Start = DATEADD(Day,1,@Start)

    SELECT @Hours = @Hours - 24

    END

    WHILE (DATEPART(WeekDay,@End)-1) % 6 = 0

    BEGIN

    SELECT @End = DATEADD(Day,1,@End)

    SELECT @Hours = @Hours - 24

    END

    SELECT @WeekDays = @Hours -ABS(DATEDIFF(Week,@End,@Start) * 48)

    SELECT @Holidays = COUNT(*) FROM tblHolidays WHERE (HolidayDate BETWEEN @Start AND @End)

    AND DATEPART(Weekday,HolidayDate)-1 % 6 <> 0 *24

    SELECT @Hours = @WeekDays - @Holidays

    RETURN(@Hours)

    END

  • quote:


    Here's a user defined function that does what you need. You will have to create tblHolidays and populate it with all the holidays you want excluded.


    
    
    sp_helplanguage

    As you cannot use SET DATEFIRST in a UDF, you may want to revise the DOW logic to work with @@DATEFIRST...

    --Jonathan



    --Jonathan

  • This function works for calculating business hours between two dates, does anyone has a function for calculating number of business days between two dates excluding holidays too.

  • why can't u use the post by NPeeters. Thats how u should be doing."Holidays" vary by divsion by company by location. So having a holiday table would be the best way to handle any situation.

    Thanks

    Sreejith

  • As suggested, do a search on this forum... there's a particullarly good and nasty fast function to calculate Work Days (week days, actually) at http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp

    Also as suggested, you will need to incorporate a "Holiday Table"... you can find out how to do that (and more with calendar tables and the like) at

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    Adam Mechanic was the guy who turned me on to Tally or Numbers tables.  Haven't needed a full blown calendar table but have made a holidy table.  It's definitely worth the read... please ignore the fact that he used an (ungh! ) cursor for one thing... he must'a been feeling poorly that day

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes, that function is OK, but only for English speaking servers.

    Change SQL Server collation - and it will fail.

    _____________
    Code for TallyGenerator

  • Then, simply change the English days of the week to something the non-English speaking server understands.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • That means an application using this function is not really portable...

    _____________
    Code for TallyGenerator

  • It is if you make it table driven for the days of the week based on language...

    But I get your point... to be portable, you would have to use one of those "trick" date-first calculations that would make up for whatever DATEFIRST is set to without actually setting date first...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Nothing is tricky here.

    (datepart(dw, @Date)+ @@datefirst)%7 is constant and does not depend on datefirst.

    By adding a parameter:

    (datepart(dw, @Date)+ @@datefirst+6-@N)%7+1

    you may set up any datefirst you want without changing database settings.

    If you set @N = 1 it will return 1 for Monday, 2 for Tuesday.

    Set @N = 7 and you'll get 2 for Monday, 3 for Tuesday.

    _____________
    Code for TallyGenerator

  • That's why "trick" was in quotes

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I knw its very old thread but leaving this query link for someone who needs it still now

    Calculate Leave working days in a table column as a default value--updated

    If you are using SQL here is the query which can help you:

    http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a/file/68664/1/02-Calculate%20Leave%20working%20days%20in%20a%20table%20column%20as%20a%20default%20value.sql

    Regards,
    Sarabpreet Singh 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply