Post reply

How To Omit Saturday & Sunday While Using DateDiff Function

  • June 16, 2008 at 1:32 am

    #189129

    hi all,

    In my task i have to count the number of dates between the two given date, i find using DateDiff function, but i have to discard the Saturdays and Sundays from my result, in the following code im getting the count as 15 for the dates start from June 1 to June 16, but i have to omit saturday and sunday, i want the result as 10, how can i achieve this..

    SELECT DATEDIFF(dd,'06/01/08', '06/16/08') AS Count

    Count

    -----------

    15

    (1 row(s) affected)

    thanks in advance,

    please guide me.

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming     🙂

  • June 17, 2008 at 1:09 am

    #829620

    Hi,

    Hope this will be helpful to you

    with fulldate (date)as

    (

    select dateadd (day,n,'06/01/08')

    from Dbo.Tally

    where n<=datediff(day,'06/01/08', '06/16/08') and dateadd(day,n,'06/01/08')<'06/16/08'

    )

    ,leave(date) as

    (

    SELECT DATEADD(DAY, 7 * N, '06/01/08') AS LeaveDay

    FROM dbo.Tally

    WHERE N <= DATEDIFF(WEEK, '06/01/08', '06/16/08')

    union

    SELECT DATEADD(DAY, 7 * N, '06/06/08')

    FROM dbo.Tally

    WHERE N <= DATEDIFF(WEEK, '06/01/08', '06/16/08')

    )

    select count(FULLDATE.date) from fulldate LEFT join leave on fulldate.date=leave.date where LEAVE.DATE IS NULL

    --you can use this following code to create a tally table

    --===== Create and populate the Tally table on the fly to replace loops

    SELECT TOP 11000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

    GRANT SELECT ON dbo.Tally TO PUBLIC

    Regards,

    Rajesh

  • June 17, 2008 at 6:00 am

    #829693

    Are you also ultimately wanting to omit holidays?

  • June 17, 2008 at 6:46 am

    #829717

    Single forumula, if you want... Also, check out some of the other folks formula's in the discussions that followed. If you do ultimately want holidays to be excluded, as well, you'll need to build a holiday table, at the very least...

    http://qa.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

    --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)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

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