Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    7

    Unanswered: Datediff for Weekdays

    I am in need of calculating the number of weekdays between 2 dates in a query. I have tried the

    Datediff('w',#09/20/2013#,#10/01/2013#)

    And I get 1, for the number of 'actual day of week for one of the dates' between the 2 dates. I want to get 7 for the number of weekdays between the 2 dates.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I found this bit of code on another forum. It is a very basic calculation, meaning it does not take holidays into account. For that you would need a holiday table that you have join against to determine if a weekday should be included or not. But it should get you going.

    Code:
    DECLARE @DateOld datetime, 
            @DateNew datetime
            
    SET @DateOld = '2013-09-20'
    SET @DateNew = '2013-10-01'
    
    SELECT DATEDIFF (day, @DateOld, @DateNew) - (2 * DATEDIFF(week, @DateOld, @DateNew)) - 
         CASE WHEN DATEPART(weekday, @DateOld + 1) = 1 THEN 1 ELSE 0 END - 
         CASE WHEN DATEPART(weekday, @DateNew + 1) = 1 THEN 1 ELSE 0 END

  3. #3
    Join Date
    May 2013
    Posts
    7
    Thanks for the answer.
    The dates are not real. I was using them as an example. I have a table with multiple dates for each record, and multiple LOANS.


    LOAN Date DateDiff
    1234567891 09/20/13 NULL (1st date)
    1234567891 09/23/13 X
    1234567891 09/30/13 X
    4567891232 10/03/13 NULL (1st date)
    4567891232 10/12/13 X
    4567891232 10/15/13 X
    4567891232 10/31/13 X
    etc. etc.

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by gsbatch1 View Post
    Thanks for the answer.
    The dates are not real. I was using them as an example. I have a table with multiple dates for each record, and multiple LOANS.
    Well, that is a completely different question. For the future, you may want to help those who can answer your question by providing consumable DLL and insert statements like this:

    Code:
    declare @loans table
    (
       Loan_ID char(11),
       Loan_Date DATETIME,
       Loan_isFirstDate char
    )
    
    insert @loans (loan_id, loan_date, loan_isFirstDate)   values
        ('1234567891', '09/20/13', NULL)
       ,('1234567891', '09/23/13', 'X')
       ,('1234567891', '09/30/13', 'X')
       ,('4567891232', '10/03/13', NULL)
       ,('4567891232', '10/12/13', 'X')
       ,('4567891232', '10/15/13', 'X')
       ,('4567891232', '10/31/13', 'X')
       ,('6549873149', '09/20/13', NULL)
       ,('6549873149', '09/21/13', 'X')
       ,('6549873149', '09/22/13', 'X')
       ,('6549873149', '10/01/13', 'X')
    So now the question is, we have to get the first loan and then the last loan and then compute the weekdays that have elapsed.

    Try the following and see what happens.

    Code:
    SELECT Loan_ID, firstDate, lastDate,
       DATEDIFF (day, firstDate, lastDate) - (2 * DATEDIFF(week, firstDate, lastDate)) - 
            CASE WHEN DATEPART(weekday, firstDate + 1) = 1 THEN 1 ELSE 0 END - 
            CASE WHEN DATEPART(weekday, lastDate + 1) = 1 THEN 1 ELSE 0 END as DaysElapsed
      FROM
         (
           Select l.Loan_ID, firstDate = MIN(l.Loan_Date), lastDate = MAX(l.Loan_Date)
             From @loans l
            Group by l.Loan_ID
         ) x;
    Last edited by LinksUp; 10-02-13 at 18:02.

Posting Permissions

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