Niggling Cold Fusion issue resolved

I have been experiencing a vexing database issue that I finally resolved this week.

I have a database with a field titled "reviewed_date". It keep periodically resetting itself to "10/01/2006".

At first (and for quite a while), I blamed it on a system problem, but the problem was actually mine!

Earlier I posted on the "DATEDIFF" function. When I started using it, some rows did not have the "reviewed_date" field populated and so the function failed. So I created a utility piece of CF code to populate all NULL fields to "10/01/2006".

I moved this code to production and never removed it.

Meanwhile I weekly took a backup of this particular table.

This past Friday I noticed that the table was intact with no issues. Monday when I came in all the "reviewed_date" fields had been reset to "10/01/2006".

Wednesday I scanned for all occurances of "10/01/2006" in my *.CFM code and noticed that this utilize code was still in production. I had used it once! I purged it from production and pondered the situatution. I hypothesized that something had been running that code in an automated way periodically. My manager scoffed at my hypothesis.

To test my hypothesis, I created a sample table, that mirrored my table that was perioically corrupted. Then I created code - aptly named hypothesis.cfm - that would update that table and reset the "reviewed_date" column to "10/01/2006". I set the "reviewed_date" values to my birthday "08/19/1949". The hypothesis.cfm code also included a CFMAIL section that would send me an email when it executed.

My hypothesis was that a regular indexing of our corporate Intranet was causing that code to execute.

Today at 10:02 am that code executed and sent me an email. The column "reviewed_date" had been set to "10/01/2006".

This seemingly small detail had vexed me for months. And I thank God that I got it resolved!

There's a programming lesson in this story!

Cold Fusion DATADIFF function

I did something new with Cold Fusion today at work and I pass this on:

1. Problem: Need to calculate a time interval between dates.
   
2. Solution: datadiff()
   

In my case, I had to display an icon based on the date calculation. A green dot if the date was less than 91 days; a yellow dot for 91-180 days, and a red dot for 181+ days.

datediff() calculates date intervals of seconds, minutes, hours, days, weeks, months or years. I used the days. Sample code follows:


<cfset days_since_reviewed = #datediff("d",reviewed_date,dateformat(now(),'mm/dd/yyyy'))#>
<cfif #days_since_reviewed# gt 180>
<cfset reviewed_image="red_dot.gif">
<cfelseif #days_since_reviewed# gt 90 AND #days_since_reviewed# lt 181>
<cfset reviewed_image="yellow_dot.gif">
<cfelseif #days_since_reviewed# lt 90>
<cfset reviewed_image="green_dot.gif">
</cfif>

Adobe Livedocs