DateDiff function

In TSQL when we and why we are using Datediff(dd,date1,date2) and  when and why Datediff(dd,date1,date2) +1
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
I give up.  Why are 'we' using one or the other?   Since I can't read your mind, and I don't see any posted code here, what exactly is your question?

DateDiff returns an integer value of the difference between days, so you can use it in an expression and add or subtract a number of days to it.    +1 is commonly used if you want the total number of days, including both date1 and date2.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Datediff(Day,date1,date2) --Day is clearer than "dd"
would return 6 if date1 was jul 1 and date2 was jul7; basically it counts the number of "nights" between days.

Datediff(Day,date1,date2) +1
would return 7 if date1 was jul 1 and date2 was jul7; it counts every day that falls within the range.
TarasAuthor Commented:
So if I understand it properly.

What is length of trip from 1 to 7 July?  I know somebody will tell it was from 1 to 7 July however it you count and calculate it lasted no longer no shorter.
That one counting only passing midnights between two date result 6 (July 1 to July 7)  and another  counting all days that come in range result  7 (July 1 to July 7) .
So it means that for somebody length of trip would be 6 days and for somebody 7 days. Or similar case for somebody from Monday to next Monday is 7 for another one 8 days.
It is up to each of us to decide how he measure length; for me it is this for you it is that for him ….
So we do not know how you doing calculation  when you give us just number 6 or 7 days of length of trip we have to ask what is included in it to know exactly what it means for us.
TarasAuthor Commented:
thanks a lot.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.