Ask a Jedi: Handling a recurring billing date

This post is more than 2 years old.

Ellis asks:

I ran into a problem with my app last week and it had me stumped for three days. I think I worked out all the kinks, but I wanted to see if you could take a glance at my code to make sure I'm on the right path. Main issue is with billing dates. When a user signs up on the app they get a billing recurring date which the same day that they sign up on ie 1/31/2010. An error was thrown 2/30/2010 because the date doesn't exist.

I took a look at Ellis' solution, and while it worked, it was quite complex and long and I suggested a much simpler solution that I thought my other readers may enjoy. Obviously there are multiple ways of handling this situation, but I recommended the following pseudo-code as a solution:

Given that a user wants to be billed on date X, and given it is Month M, Year N, what is the best possible match? If M/X/N exists, then use it. If the month doesn't have X days, then use the last day of the month.

I wrote this logic as the following simple UDF:

<cfscript> function getBillingDate(month,year,day) { var baseDate = createDate(arguments.year, arguments.month, 1); if(daysInMonth(baseDate) lt arguments.day) return createDate(arguments.year, arguments.month, daysInMonth(baseDate)); return createDate(arguments.year, arguments.month, arguments.day); } </cfscript>

As you can see, it creates a date based on the passed in year and month. It uses 1 for the day of the month. Once we have that, I simply compare the days in the month to the desired day. If the days in the month is less than the desired date, I use the total number of days in the month. Otherwise - I use the desired date.

To ensure it actually worked, I whipped up a quick test. It runs through five years and a set of desired dates. I intentionally chose dates towards the end of the month to test my logic.

<cfset tests = [1,10,15,30,31]>

<cfloop index="year" list="2000,2001,2002,2003,2004"> <cfloop index="month" from="1" to="12"> <cfloop index="testDate" array="#tests#"> <cfoutput> Attempted billing day of #testDate# for #month#/#year# : #getBillingDate(month,year, testDate)#<br/> </cfoutput> </cfloop> <br/><br/> </cfloop> <br/><br/> </cfloop>

I won't bore people with the output from this, but I confirmed it correctly handled February, and also noticed leap years when it could get a bit closer to 30 and 31.

I'm sure there are probably a thousand other ways to handle this, but hopefully this will help others.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Chuck Savage posted on 2/10/2010 at 6:33 AM

I handled something like this in Flex a while ago, for different reasons but it had to do with date ranges. Of course there are other ways, but ActionScript in Flex has the ability to add/subtract days/months/years. If I want the last day of a month (say of February 2010) I set the date to March 1st of 2010 and subtract 1 day. Can ColdFusion do the same?

Comment 2 by Raymond Camden posted on 2/10/2010 at 6:39 AM

Yep - dateAdd().

Comment 3 by Aaron Holesgrove posted on 2/10/2010 at 6:56 AM

Hi Ray - in my apps I strictly force reoccurring billing to happen between the 1st and 28th of each month - if the current day is greater than 28 on signup, I change it to happen on the 28th regardless so that the customer can have a consistent reoccurring billing date to depend on. Companies tend to like this logic too for their customers. Your cfscript example is spot on though, if you're forced to work with the limitations of the days in each month, nice job.

Comment 4 by Leigh posted on 2/10/2010 at 6:17 PM

I think people often get thrown by date problems. Then end up complicating things beyond belief, just out of confusion. This is a great example of how simple and elegant a solution can be with the use of the right date functions.

Comment 5 by Daniel Budde posted on 2/10/2010 at 7:50 PM

Same as Aaron, we let the customer choose their payment date on a finance website we have done work for, but for consistency we only let them choose between the 1st and the 28th. I also agree, your solution appears to be elegant enough.

I've found the complexity increases some when people want to change payment days and tracking successful payments. In our system, there is an added check to make sure there are no existing successful payments already in the same month. Also, the recurring payments are disabled after 2 failed attempts within the same month and a notice is sent out to the customer and to an account rep.

Comment 6 by David Hammond posted on 2/10/2010 at 8:14 PM

Here's a function that gets the next monthly billing date based on the original start date, which can be any date in the past:

<cffunction name="GetNextBillingDate" returntype="date" output="false">
<cfargument name="startdate" type="date" required="true">
<cfargument name="currentdate" type="date" default="#Now()#">
<cfreturn DateAdd("m", DateDiff("m", startdate, currentdate) + 1, startdate)>
</cffunction>

Interestingly, my original code was in SQL, which also has the DateDiff function but works in such a way that the calculation is slightly more complicated.

Comment 7 by David Herman posted on 2/10/2010 at 11:41 PM

Even simpler we just bill every 28 days from their signup. Our billing periods are ALL day based be it 28, 90 180 or 365

Comment 8 by David Herman posted on 2/10/2010 at 11:44 PM

btw Ray, what are the 2 ads I'm seeing in the lower right... they dont look like something thats supposed to be there :)

# Lap Band Surgery
# cheap web hosting

Comment 9 by Raymond Camden posted on 2/10/2010 at 11:46 PM

Text-Link-Ads.com. Kinda weird, but they pay super well. It helps me pay for my video games (when folks don't visit my wish list enough ;).

Comment 10 by Rob Cawte posted on 2/16/2010 at 10:40 AM

Paypal handles this issue by moving the payment date to the first of the next month whenever the payment date does not exist. Similarly for annual subscriptions they move to March 1st if the original sub was created on Feb 29th.

From their pdf:
https://cms.paypal.com/cms_...

How Subscriptions with Monthly Billing Cycles Work
For monthly billing cycles, recurring payments are collected on the same day of the month. If the initial recurring payment falls on the 31st, PayPal eventually adjusts the billing cycle to the 1st of the month. If the initial recurring payment falls on the 29th or 30th, PayPal adjusts the billing cycle to the 1st of the month on the following February.

EXAMPLE 3.2 When Monthly Recurring Payments Are Due and Collected on the 31st
The subscription terms are:
- $25.99 USD a month; the subscriber signs up on Thursday, July 31.

The subscriber is billed as follows:
- Thursday, July 31 = $25.99 USD
- Saturday, August 31 = $25.99 USD
- Wednesday, October 1= $25.99 USD
- Saturday, November 1= $25.99 USD
and so on...

Notice that no recurring monthly payment was collected in September, but recurring payments were collected roughly every 30 days.

EXAMPLE 3.3 When Monthly Recurring Payments Are Due and Collected on the 30th
The subscription terms are:
- $25.99 USD a month; the subscriber signs up on Tuesday, December 30.
The subscriber is billed as follows:
- Tuesday, December 30 = $25.99 USD
- Friday, January 30 = $25.99 USD
- Sunday, March 1= $25.99 USD
- Wednesday, April 1= $25.99USD
and so on...

Notice that no recurring monthly payment was collected in February, but recurring payments were collected roughly every 30 days.