datediff without weekends

How would I use the datediff function without counting wends?
declare @.StartDate SmallDateTime
declare @.EndDate SmallDateTime
set @.StartDate = '10/01/05'
set @.EndDate = getdate()
select datediff(d,@.StartDate,@.EndDate)
--except for the wends
This value returns 18, but I want it to not count wends so it should only
return 13.
--
TIA,
ChrisRChris,
Take a look at:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_threa
d/thread/fc6d9c7aa9b2580d/af9e9a9f851db285?lnk=st&q=Date+Difference+without+wen
ds&rnum=1&hl=en#af9e9a9f851db285[/url]
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||> set @.StartDate = '10/01/05'
Egads, this is a very ambiguous date format. I highly recommend using a
more standard format, e.g. YYYYMMDD, that cannot suddenly break if you
change your regional settings or run your code in a database with a
different language or dateformat setting...

> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
Use a calendar table, then you can incorporate holidays and other
non-working days too.
http://www.aspfaq.com/2519|||Look up how to design a Calendar table, which is a more general tool
for all of the temporal queries you will do.|||This link may help with Joe's suggestion.
http://www.aspfaq.com/show.asp?id=2519
HTH
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129763725.019473.177630@.o13g2000cwo.googlegroups.com...
> Look up how to design a Calendar table, which is a more general tool
> for all of the temporal queries you will do.
>|||Hey, Jerry, make the lazy bum work for it !! Google is a good habit
instead of the "do my homework for me" mentality that floods the
Newsgroups.|||Chris,
If you only want to exclude wends, here's one way to achieve this
(assuming Monday as the first day of the w):
SELECT
days/7*5 + days%7
- CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
- CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
DATEDIFF(day, @.StartDate, @.EndDate) + 1 AS days,
DATEPART(wday, @.StartDate + @.@.DATEFIRST - 1) AS wd
) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||Thanks to all...
--
TIA,
ChrisR
"ChrisR" wrote:

> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should on
ly
> return 13.
> --
> TIA,
> ChrisR