Traditionally in ColdFusion, while you go back a NULL date from the database, the CFQuery tag interprets that NULL date as [empty string]. This has at all times made it slightly simple to control dates within the utility common sense as a result of all you wish to have to do is go the price into the isDate() choice serve as ahead of you employ it. Lately, the CFQuery tag has added a returnType characteristic that permits the database recordset to be returned as both an Array-of-Structs or a Column-based Struct (Lucee CFML most effective). Sadly, when returning the question as an Array, Adobe ColdFusion not interprets NULL dates into empty-strings.

To peer this in motion, I will hard-code a SELECT SQL remark that returns NULL for a “date column”. Then, we will output the end result and take a look at to get entry to mentioned date:

<!--- NOTE: We are hard-coding a NULL date and returning an Array. --->
<cfquery title="information" returnType="array" datasource="checking out">
	SELECT
		( 1 ) AS identification,
		( 'Planet Health' ) AS title,
		( NULL ) AS lastUsedAt
	;
</cfquery>

<cfoutput>

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

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

	<!---
		Traditionally, when returning a NULL-date from the database, ColdFusion will
		translate the price as an empty-string, making it really easy to paintings with. When
		returning the question as an ARRAY, then again, Adobe ColdFusion turns out to translate the
		NULL-date as a NULL-value (no longer the empty string). As such, making an attempt to reference
		it'll lead to a null-reference error (NRE).
	--->
	<cftry>
		<p>
			Remaining Used: #information.first().lastUsedAt#
		</p>

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

</cfoutput>

As you’ll see, we are CFDumping-out the price after which seeking to reference the lastUsedAt price (which we all know to be NULL). And, once we run this in each Adobe ColdFusion (2021 and 2023) and Lucee CFML, we get the next output:

As you’ll see, Lucee CFML returns the NULL date price because the empty string (as we might think given ColdFusion’s historic conduct). Adobe ColdFusion, then again, returns it as NULL, because of this that the consequent struct contained an undefined key. Which is why we get an error when pass to output the NULL date price.

In my view, I choose the Lucee CFML conduct. It’s a lot more in alignment with how the CFQuery tag has labored traditionally. If one had been to allow Complete-Null Toughen within the CFML engine, I’d perceive the Adobe ColdFusion (ACF) reaction; however, with out mentioned environment enabled, the ACF conduct provides useless friction to the intake workflow.

Need to use code from this publish?
Take a look at the license.