SQL Count Years on FromDATE to ToDate fields

Question

Hi again,

Suppose i have

> ID              FromDate          ToDate 
> --              --------           -------
> 1               01/01/2005         30/6/2007
> 2               01/01/2008         31/12/2009

I want to count the years that are included on this 2 rows. Here is 1,5 years for the first row + 2 years from second row = Total of 3,5 years. How can i do this with SQL?

Answer 1

I think your best bet is to use DATEDIFF ( datepart , startdate , enddate)

Select ID, DATEDIFF(mm,StartDate,EndDate)/12 from YourTable

this should give the result your after.

Edit: Assuming SQL Server

Answer 2

I think you have you calculations wrong - the first should be 2.5 years? If so this seems to give a decent approximation of what you want (again in SQL server)

create table YearDiff(ID int, fromDate datetime, toDate datetime)
go
insert into YearDiff values (1, '2005-01-01 00:00:00.000', '2007-06-30 00:00:00.000')
insert into YearDiff values (2, '2008-01-01 00:00:00.000', '2009-12-31 00:00:00.000')
select sum((datediff(ww, fromdate, todate)) / 52.0)  from YearDiff

Answer 3

If you're using Oracle:

SELECT TRUNC(SUM(MONTHS_BETWEEN(t.todate, t.fromDate)) / 12, 1)
  FROM TABLE t

The second parameter in TRUNC is the number of decimal places - the query, as is, would be to one decimal place.

Answer 4

I would recommend using 365.25 for the days in the SQL DateDiff function to avoid issues with leap year.

select 
  SUM((DATEDIFF(d, FromDate, ToDAte)) / 365.25) as 'years-count'
from 
  mytableofdates