You're on the right track Carol:

SELECT DATEDIFF(yyyy,@Date1,@Date2)-
CASE WHEN YEAR(@Date2) > YEAR(@Date1) AND
((MONTH(@Date2) < MONTH(@Date1)) OR
(MONTH(@Date2) = MONTH(@Date1) AND
DAY(@Date2) < DAY(@Date1)))
THEN 1
ELSE 0
END

Trevor


----- Original Message -----
From: Carol Green <mssqldba-ezmlmshield-
x58602917.[Email address protected]
Date: Friday, February 24, 2006 7:27 am
Subject: DATEDIFF - strange behaviour?

> Hmm,
>
> I was thinking of using the datediff function to calculate length of
> service in years, but I started to notice some strange results:
>
> A: DATEDIFF(yyyy, '06/01/2005', '01/01/2006') = 1
> B: DATEDIFF(yyyy, '06/01/2005', '12/31/2006') = 1
> C: DATEDIFF(yyyy, '06/01/2005', '01/01/2007') = 2
> D: DATEDIFF(yyyy, '12/31/2005', '01/01/2007') = 2
>
> whereas
>
> E: FLOOR(DATEDIFF(dd, '06/01/2005', '01/01/2006')/365) = 0
> F: FLOOR(DATEDIFF(dd, '06/01/2005', '12/31/2006')/365) = 1
> G: FLOOR(DATEDIFF(dd, '06/01/2005', '01/01/2007')/365) = 1
> H: FLOOR(DATEDIFF(dd, '12/31/2005', '01/01/2007')/365) = 1
>
> In example A, the datediff value = 1 despite the person having worked
> only 6 months. Example B calculates correctly (18 months service) but
> add one more day (example C) and the value goes to 2. Even worse, in
> example D, the person has worked only just over 12 months but
> shows as
> having 2 years' service. The DATEDIFF function seems to just be
> calculating the number of 1st of Januaries between the dates.
>
> Examples D,E,F and G give me the results the I am looking for but
> wouldn't take into account leap years.
>
> Is there a better way to calculate the number of whole years
> between two
> dates?
>
> Carol.
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
> This email and any files transmitted within it are private and
> confidential.
> If you are not the intended recipient, this email and any
> attachments within it have been transmitted to you in error.
> You are prohibited from using, copying, distributing or otherwise
> using the information contained within this email.
> Nothing in this email message amounts to a contractual or legal
> commitment on the part of Optilan unless confirmed by a
> communication signed on behalf of the company.
> Any email cannot be guaranteed to be secure, error free or free
> from viruses. Although every possible care is taken by Optilan,
> Optilan does not accept any liability whatsoever for any loss or
> damage which may be caused as a result of the transmission of this
> message by email.
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

MS Sql Server LazyDBA home page