Hi,
SQL Server 2000
-------------------
I have a requirement to calculate the elapsed years by comparing two
datetime columns.

The following SELECT statement works for most of the rows

select (cast(date1-date2) as int)/365 as [elapsed years] from mytable

But, it returns the value 1 instead of 0 when I compare Feb 28, 2004 to Feb
27, 2005. Is there an easy way to handle such situations?
Thanks,

See if this works for you:

select
year(date1)-year(date2) -
case when 100*month(date1)+day(date1)
< 100*month(date2)+day(date2)
then 1 else 0 end as yearsElapsed

It calculates the difference in calendar years first, which is already
correct if the later month/day is on or after the earlier month/day,
ignoring the year, but is one year too great if the later month/date is
before the earlier month/date, ignoring the year.

Anith Sen suggested this more compact solution a while back:

declare @d1 datetime
declare @d2 datetime
set @d1 = '20040229'
set @d2 = '20000229'
select
(0 + convert(char(8),@d1,112)
- convert(char(8),@d2,112)
) / 10000


Steve Kass
Drew University


[quoted text, click to view]