DECLARE @start datetime,@end datetime
SELECT @start='1-oct-2006',@end='10-oct-2007'
SELECT CASE WHEN MONTH(@end)<MONTH(@start)
                 OR DAY(@end)<DAY(@start)
            THEN DATEDIFF(yy,@start,@end)-1
            ELSE DATEDIFF(yy,@start,@end) 
       END AS years,
       CASE WHEN  DAY(@end)<DAY(@start)
            THEN DATEDIFF(mm,DATEADD(yy,DATEDIFF(yy,0,@end),0),@end)-1
            ELSE DATEDIFF(mm,DATEADD(yy,DATEDIFF(yy,0,@end),0),@end) 
       END AS months,
       DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,0,@end),0),@end) AS Days

       

quote:

---

Originally posted by gsrinivas.

thanks for quick reply.
however my doubt is a result as single query....
ok, thanks for code..

i have another formula in single query....
but it hasn't exact output...
my formula is ..
*)My actual problem is ... find the experience of employee in
years,months,and days.(hiredate,getdate())

i found some formula which is not exact...
suppose the total days from different dates are 3862.

365)3862(10 -----(A)
----------3650
---------------
--------30)212(7-------(B)
-----------210
---------------
---------- 2 ------(C)

hahaha... this is the output 10 years 7 months 2 days.
but this may or may not correct..because the year may be a leap
year.. and i have taken month have exact 30 days which is wrong every
time......
by using this formula we can write a single query...but may not exact result..

however is your code outputs the exact result.....?
i have to check at home....hope it is working...
thank you very much for your quick reply.

-GSrinivas

---