Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

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

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

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

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

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

Powered by:
Powered By Subtext Powered By ASP.NET