# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## 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
AS
BEGIN
RETURN  CASE
WHEN @ToDate < DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
ELSE DATEDIFF(MONTH, @FromDate, @ToDate)
END
END
GO
CREATE FUNCTION [dbo].[fnYearsApart]
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS INT
AS
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

Print | posted on Friday, February 13, 2009 10:50 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

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

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 |

## #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.