Improved anniversary calculation (better datediff)

Fri Feb 13, 2009 by Peter Larsson in optimization, sql-server-2008, algorithms, sql-server-2005, sql-server-2000

Some time ago, I wrote this article about how DATEDIFF works.
http://www.sqlteam.com/article/datediff-function-demystified
At the end I suggested two functions to calculate the number of months according to how human mind works.

At the discussion later, a person notified me that it calculated the wrong number of months if you tried January 29th 2009 to February 28th 2009.
The day is still greater, but you cannot have more days in february 2009 than 28.

These are improved functions that also deals with those situations.

CREATE FUNCTION [dbo].[fnMonthsApart]

(

@FromDate DATETIME,

@ToDate DATETIME

)

RETURNS INT

BEGIN

RETURN CASE

WHEN @ToDate < DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1

ELSE DATEDIFF(MONTH, @FromDate, @ToDate)

END

CREATE FUNCTION [dbo].[fnYearsApart]

(

@FromDate DATETIME,

@ToDate DATETIME

)

RETURNS INT

BEGIN

RETURN CASE

WHEN @ToDate < DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(YEAR, @FromDate, @ToDate) - 1

ELSE DATEDIFF(YEAR, @FromDate, @ToDate)

END

END
GO

DECLARE @Sample TABLE

(

FromDate DATETIME,

ToDate DATETIME

)

INSERT @Sample

SELECT '20090228', '20090327' UNION ALL

SELECT '20090228', '20090328' UNION ALL

SELECT '20090228', '20090329' UNION ALL

SELECT '20090228', '20090330' UNION ALL

SELECT '20090228', '20090331' UNION ALL

SELECT '20090126', '20090227' UNION ALL

SELECT '20090127', '20090227' UNION ALL

SELECT '20090128', '20090227' UNION ALL

SELECT '20090128', '20090228' UNION ALL

SELECT '20090129', '20090228' UNION ALL

SELECT '20090130', '20090228' UNION ALL

SELECT '20090131', '20090228'

SELECT FromDate,

ToDate,

dbo.fnMonthsApart(FromDate, ToDate) AS Diff

FROM @Sample

Legacy Comments


Remote DBA 2009-02-13	re: Improved anniversary calculation (better datediff) datediff operations have always been driving me crazy :) . It took half hour to understand what your functions actually do :)

Sander 2009-10-29	re: Improved anniversary calculation (better datediff) How about this? FLOOR((Todate-Fromdate)/10000) The key is to use the yyyymmdd notation cast as an Integer

Peso 2009-10-29	re: Improved anniversary calculation (better datediff) With integer math, I guess this should work (1 + CAST(CONVERT(CHAR(8), @ToDate, 112) AS INT) - CAST(CONVERT(CHAR(8), @FromDate, 112) AS INT)) / 10000

Hans 2010-06-01	re: Improved anniversary calculation (better datediff) Hello Peso, Why the "1+" at the start of your age calculation? Regards, Hans

Peso 2010-06-01	re: Improved anniversary calculation (better datediff) I don't remember right now, but I think it has to do with February 29 calculations.