scottichrosaviakosmos (6/29/2010)
i want to get date difference like a start date and end date and find difference in years but i want like if for example one date is 2007 and end date is 1 jan 2010 then the difference is of 3 years and one day so sql is showing 3 year .. but i want it to show 4 years. i.e even one day is extra should add that also.
The year count increments with year boundaries;
SELECT DATEDIFF(dd, '31-12-2009', '01-01-2010') -- 1 day
SELECT DATEDIFF(yy, '31-12-2009', '01-01-2010') -- 1 year
It might be more useful for you to count months instead:
set dateformat dmy
SELECT DATEDIFF(mm, '31-12-2006', '31-12-2009') -- 36
SELECT DATEDIFF(mm, '31-12-2006', '01-01-2010') -- 37
SELECT DATEDIFF(mm, '01-01-2009', '31-12-2009') -- 11
SELECT DATEDIFF(mm, '01-11-2009', '31-01-2010') -- 2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden