gringogordo
asked on
Calculating the date difference in years
Hi,
I think there must be a straightforward answer to this but its escaping me!
I want to work out the difference between 2 dates in years. I don't want to use datediff('yy'... because this does a year() calculation so 01/jan/2001 is a year on from 31/dec/2000. I want to do it on the basis that a year is 365 days. so
01/jan/2001 - 31/dec/2000 = 0 years
01/jan/2001 - 31/dec/2001 = 1 year
22/mar/2005 - 21/mar/2005 = 1 year
and so on so basically I want to do a datediff(dd, start, end) / 365 but I would like to take account of the leep year.
Any suggestions?
Thanks
I think there must be a straightforward answer to this but its escaping me!
I want to work out the difference between 2 dates in years. I don't want to use datediff('yy'... because this does a year() calculation so 01/jan/2001 is a year on from 31/dec/2000. I want to do it on the basis that a year is 365 days. so
01/jan/2001 - 31/dec/2000 = 0 years
01/jan/2001 - 31/dec/2001 = 1 year
22/mar/2005 - 21/mar/2005 = 1 year
and so on so basically I want to do a datediff(dd, start, end) / 365 but I would like to take account of the leep year.
Any suggestions?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Perhaps, although it will have some involved code in it. You will need a table with all possible year numbers in them (1990, 1991, ..., 2006, ..., 2050): since a table of sequential numbers is so valuable anyway, you might as well create that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Standard datediff, by year. Then I Add a -1 to the year for those cases where the PreviousDate has not happened yet.
ASKER
Sorry I must have been going mad. In case someone is looking at this again
datediff(yy, 0, (MostRecentDate - PreviousDate) - 1)
doesn't work
SELECT datediff(yy, 0, (cast('31 Dec 2003' as datetime - cast('31 Dec 2002' as datetime) - 1)
comes back as 0 instead of 1.
datediff(yy, 0, (MostRecentDate - PreviousDate) - 1)
doesn't work
SELECT datediff(yy, 0, (cast('31 Dec 2003' as datetime - cast('31 Dec 2002' as datetime) - 1)
comes back as 0 instead of 1.
Oops. In my app, dates weren't inclusive. Remove the "-1" from the end of the formula. This will make 2/29/2004 to 2/28/2006 = 2, but in most instances, that will be acceptable.
ASKER