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:
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.