Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Amsterdam) with: Tom de Manincor
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Amsterdam) with: Tom de Manincor

Adobe ColdFusion Returns NULL Dates As NULL When Using CFQuery ReturnType "Array"

By
Published in Comments (3)

Historically in ColdFusion, when you return a NULL date from the database, the CFQuery tag translates that NULL date as [empty string]. This has always made it quite easy to manage dates in the application logic because all you need to do is pass the value into the isDate() decision function before you use it. In recent years, the CFQuery tag has added a returnType attribute that allows the database recordset to be returned as either an Array-of-Structs or a Column-based Struct (Lucee CFML only). Unfortunately, when returning the query as an Array, Adobe ColdFusion no longer translates NULL dates into empty-strings.

To see this in action, I'm going to hard-code a SELECT SQL statement that returns NULL for a "date column". Then, we'll output the result and try to access said date:

<!--- NOTE: We're hard-coding a NULL date and returning an Array. --->
<cfquery name="data" returnType="array" datasource="testing">
	SELECT
		( 1 ) AS id,
		( 'Planet Fitness' ) AS name,
		( NULL ) AS lastUsedAt
	;
</cfquery>

<cfoutput>

	<p>
		#server.coldfusion.productName# :
		#server.coldfusion.productVersion#
	</p>

	<cfdump
		var="#data.first()#"
		label="ReturnType: Array"
	/>

	<!---
		Historically, when returning a NULL-date from the database, ColdFusion will
		translate the value as an empty-string, making it very easy to work with. When
		returning the query as an ARRAY, however, Adobe ColdFusion seems to translate the
		NULL-date as a NULL-value (not the empty string). As such, attempting to reference
		it will result in a null-reference error (NRE).
	--->
	<cftry>
		<p>
			Last Used: #data.first().lastUsedAt#
		</p>

		<cfcatch>
			<p>
				Error: #cfcatch.message#
			</p>
		</cfcatch>
	</cftry>

</cfoutput>

As you can see, we're CFDumping-out the value and then trying to reference the lastUsedAt value (which we know to be NULL). And, when we run this in both Adobe ColdFusion (2021 and 2023) and Lucee CFML, we get the following output:

Null reference error caused by null date in Adobe ColdFusion, works fine in Lucee CFML.

As you can see, Lucee CFML returns the NULL date value as the empty string (as we would assume given ColdFusion's historical behavior). Adobe ColdFusion, on the other hand, returns it as NULL, which means that the resultant struct contained an undefined key. Which is why we get an error when go to output the NULL date value.

Personally, I prefer the Lucee CFML behavior. It is much more in alignment with how the CFQuery tag has worked historically. If one were to enable Full-Null Support in the CFML engine, I would understand the Adobe ColdFusion (ACF) response; but, without said setting enabled, the ACF behavior adds unnecessary friction to the consumption workflow.

Want to use code from this post? Check out the license.

Reader Comments

40 Comments

We have SO much code that is bascally:
<cfif structKeyExists(local.thing, "key") AND len(trim(local.thing.key)) GT 0>

Like...SO much. I almost wish we had a base.cfc function called hasValue(struct, key).

15,848 Comments

@Will,

I feel your pain! With recent releases of ColdFusion, at the very least, we can use the Elvis operator (?:) to set some fallback to make some things easier. I just wrote code today like:

if ( allowlist[ key ] ?: 0 ) {
	// ....
}

It doesn't always make sense (and Adobe ColdFusion has some strange rules around it); but, it has helped to simplify some workflows.

Post A Comment — I'd Love To Hear From You!

Post a Comment

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel