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 return createDate(arguments.year, arguments.month, daysInMonth(baseDate)); return createDate(arguments.year, arguments.month,; } </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 developer advocate for HERE Technologies. He focuses on JavaScript, serverless 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