October 18, 2007 at 3:46 am
I need to calculate the number of days between 1st September 2003 & 8th September 2003. Report Designer is giving me 7 back as an answer when it is actually only 5 as there is a weekend in the middle
October 18, 2007 at 6:27 am
This is from and article by Jeff Moden, and is pretty nifty!
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
SELECT
(DATEDIFF(dd, '9/1/2007', '9/8/2007') + 1)
-(DATEDIFF(wk, '9/1/2007', '9/8/2007') * 2)
-(CASE WHEN DATENAME(dw, '9/1/2007') = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, '9/8/2007') = 'Saturday' THEN 1 ELSE 0 END)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 18, 2007 at 6:42 am
Thanks for getting back top me. one problem. i am using a model for my datasource. I don't have an field for day(sunday) only, Day (13th), Week, Month & year.
October 18, 2007 at 6:46 am
That's what the DATENAME function does. It converts the date you supply with the actual DATE NAME. just replace the dates in the above query with your parameters.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 18, 2007 at 6:51 am
I got it thanks you, the dates i am working with will never start or end on a Saturday or Sunday so i used the -DATEDIFF(wk, @StartDate, @EndDate) * 2 features.
Thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply