1. Registered User
Join Date
May 2013
Posts
7

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. Registered User
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. Registered User
Join Date
May 2013
Posts
7
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. Registered User
Join Date
Dec 2012
Location
Logan, Utah
Posts
163
Originally Posted by gsbatch1
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
•