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

GO

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

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

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

## # re: Improved anniversary calculation (better datediff)

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

Regards, Hans

## # re: Improved anniversary calculation (better datediff)

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