Well, i found this query really useful where not much hard coding is done and we are getting the exact output
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int, @enddate datetime
SET @date = '20120229' -- Replace with this value '20120228' and execute and check
SET @enddate = '20130228'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, @enddate) - CASE WHEN (MONTH(@date) > MONTH(@enddate)) OR (MONTH(@date) = MONTH(@enddate) AND DAY(@date) > DAY(@enddate)) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
--SELECT @months = DATEDIFF(m, @tmpdate, '20110525') - CASE WHEN DAY(@date) > DAY('20110525') THEN 1 ELSE 0 END
--SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, @enddate)
SELECT Cast(@years as varchar(3))+'.'+Right('00'+Cast(@days as Varchar(3)),3)
without much hardcoding ... 🙂 which i have started using in my work progress..
@@ Celko : Your correct...i am just a guy who started trying to know about the earth, and this is actually been used in warehouse db to make reporting purpose easfull 🙂
and I am really glad that these many experts are here to help me out Thanks once again to ALL of you for your time 🙂