Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1316
  • Last Modified:

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
0
gringogordo
Asked:
gringogordo
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi gringogordo,
use this function to check whether an year is leap year or not

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate DATETIME )
RETURNS BIT
AS
BEGIN
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1
RETURN 0
END
GO
0
 
gringogordoAuthor Commented:
Thats a nice function but the problem for me is the date difference will be over multiple years (mainly 5 to 10) so I would have to use a cursor or something to loop round the years.  I don't like that thought much, I'm hoping there is a single statement that might do the job?
0
 
Scott PletcherSenior DBACommented:
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.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LandyJCommented:
To calculate full years, use:

datediff(yy, 0, (MostRecentDate - PreviousDate) - 1)

Subtracting 1 accounts for leap year.  It adjusts the date to Mar 1 of non-Leap Years.  That is based on number of days from start of the year, days since last actual Feb 29, etc.

For ex:  
2/29/2004 to 2/28/2006 = 1
2/29/2004 to 3/1/2006 = 2

hth,
Landy
0
 
adwisemanCommented:
This is what I use.

DATEDIFF(yy, PreviousDate, MostRecentDate) +
        CASE WHEN (DATEPART(Month, MostRecentDate) < DATEPART(Month, PreviousDate)) THEN -1
             WHEN (DATEPART(Month, MostRecentDate) > DATEPART(Month, PreviousDate)) THEN 0
             WHEN (DATEPART(Day, MostRecentDate) < DATEPART(Day, PreviousDate)) THEN -1
             ELSE 0 END
0
 
adwisemanCommented:
Standard datediff, by year.  Then I Add a -1 to the year for those cases where the PreviousDate has not happened yet.
0
 
gringogordoAuthor Commented:
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.

0
 
LandyJCommented:
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.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now