Improved anniversary calculation (better datediff)

<< Get all your databases and their sizes | Home | Fastest LeapYear checker? >>

Improved anniversary calculation (better datediff)

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

Feedback

# 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 :)

2/13/2009 2:02 PM | Remote DBA

# 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

10/29/2009 1:31 PM | Sander

# 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

10/29/2009 5:08 PM | Peso

# re: Improved anniversary calculation (better datediff)

Hello Peso,

Why the "1+" at the start of your age calculation?

Regards, Hans

6/1/2010 3:10 PM | Hans

# re: Improved anniversary calculation (better datediff)

I don't remember right now, but I think it has to do with February 29 calculations.

6/1/2010 3:21 PM | Peso

Comments have been closed on this topic.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories