I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
--
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
--
Thanks
Yogish