Calculating Month Difference

Recently I got a request from a reader to calculate the difference in terms of months between two dates in order to work out how many standing order/direct debit payments should have been received. (See also, "Correctly Calculating Datetime Differences" and "Manipulate and Calculate.")

I’ll provide a solution using a scalar UDF called NumPayments. The function accepts two inputs: @startdate (the standing order/direct debit start date), and @enddate (the date that the calculation should be checked against—typically the date when the query is run). Here’s the function’s header:

CREATE FUNCTION dbo.NumPayments

(

  @startdate AS DATETIME,

  @enddate   AS DATETIME

) RETURNS INT

The logic behind the calculation is actually quite straightforward. First calculate the difference in terms of months between the starting year-and-month and the ending year-and-month, then add 1 to account for both the starting month and ending month. Then, subtract 1 in case the ending day is smaller than the starting day. Per the original request that I got, if the day part of the end date is on or after the day part of the start date, there is a payment in the end month, otherwise there isn’t. If the day part of the start date is greater than the maximum possible day part of a given month, the payment for that month happens on the first of the next month. The last part of the calculation handles this scenario correctly (subtract 1 in case the ending day is smaller than the starting day).

As an example, suppose you are given the following sample data representing different dates in which you make the request:

USE tempdb;

IF OBJECT_ID('dbo.SampleDates', 'U') IS NOT NULL

  DROP TABLE dbo.SampleDates;

CREATE TABLE dbo.SampleDates

(

  dt DATETIME NOT NULL PRIMARY KEY

);

GO

INSERT INTO dbo.SampleDates(dt)

            SELECT '20090228'

  UNION ALL SELECT '20090301'

  UNION ALL SELECT '20090429'

  UNION ALL SELECT '20090430'

  UNION ALL SELECT '20090501';

And you run the following query to calculate how many standing order/direct debit payments should have been received by the date in the table, for a start date of October 30^th, 2008:

DECLARE @startdate AS DATETIME;

SET @startdate = '20081030';

SELECT

  @startdate AS startdate, dt AS enddate,

  dbo.NumPayments(@startdate, dt) AS payments

FROM dbo.SampleDates;

You are supposed to get the following output:

startdate               enddate                 payments

----------------------- ----------------------- -----------

2008-10-30 00:00:00.000 2009-02-28 00:00:00.000 4

2008-10-30 00:00:00.000 2009-03-01 00:00:00.000 5

2008-10-30 00:00:00.000 2009-04-29 00:00:00.000 6

2008-10-30 00:00:00.000 2009-04-30 00:00:00.000 7

2008-10-30 00:00:00.000 2009-05-01 00:00:00.000 7

Here’s the definition of the UDF that I used to address this calculation:

IF OBJECT_ID('dbo.NumPayments', 'FN') IS NOT NULL

  DROP FUNCTION dbo.NumPayments;

GO

CREATE FUNCTION dbo.NumPayments

(

  @startdate AS DATETIME,

  @enddate   AS DATETIME

) RETURNS INT

AS

BEGIN

  RETURN

    DATEDIFF(month, @startdate, @enddate)

      - CASE

          WHEN DAY(@enddate) < DAY(@startdate) THEN 1

          ELSE 0

        END

      + 1;

END

GO

As you can see, it’s pretty much a literal translation of the calculation I described earlier, and as such is simple and straightforward. The code uses the DATEDIFF function to calculate the difference in terms of months between @startdate and @enddate and adds 1 to account for both starting month and ending month. The code then subtracts the result of a CASE expression that returns 1 in case the day part of @enddate is smaller than the day part of @startdate and 0 otherwise. If you run the code provided above to test the function, you will get the desired output.

Cheers,

BG