Different behavior CF / Lucee on cfquery / timestamp

There is a different behavior between CF and Lucee on how a timestamp is casted to string in a cfquery:

<cfset qry= queryNew("name,birthday", "varchar,timestamp", [
{ "name": "Susi", "birthday": CreateDateTime(1950, 4, 29, 10, 23, 0) },
{ "name": "Jack" , "birthday": CreateDateTime(1982, 9, 6, 3, 0, 0) }
<!--- <cfdump var="#qry#" /> --->
<cfquery name="q0" dbtype="query">
	select birthday from qry
<cfset curDt = now()>
q0.birthday=#q0.birthday#, correct q0.birthday string=#LSDateTimeFormat(q0.birthday, 'yyyy-mm-dd HH:nn:ss')#, curDt=#curDt#<br>

<cfset d = CreateDateTime(1950, 4, 29, 10, 23, 0) >
	a simple date =#d#

With CF the output is:

q0.birthday=1950-04-29 10:23:00.0, correct q0.birthday string=1950-04-29 10:23:00, curDt={ts ‘2024-05-03 19:41:03’}

a simple date ={ts ‘1950-04-29 10:23:00’}

With Lucee the output is:

q0.birthday={ts ‘1950-04-29 10:23:00’}, correct q0.birthday string=1950-04-29 10:23:00, curDt={ts ‘2024-05-03 19:42:05’}

a simple date ={ts ‘1950-04-29 10:23:00’}

please note the difference of formats

q0.birthday=1950-04-29 10:23:00.0
q0.birthday={ts ‘1950-04-29 10:23:00’}

Lucee versions:

  • with openjdk11_jre_x32_windows_hotspot_jdk-11.0.23+9
  • 6.1.0-SNAPSHOT+132 with jdk-21.0.3+9

CF versions:

  • adobe@11.0.19+314546 with openjdk8_jre_x32_windows_hotspot_jdk8u412-b08
  • adobe@2023.0.7.330663 with openjdk11_jre_x32_windows_hotspot_jdk-11.0.23+9

OS: Windows 11

That is maybe because you are outputting that dateObject without casting it to a string. Lucee then clearly shows you: that is a “timestamp” (ts). Any other language would throw an exception:Its not a string! It would just throw an exception just like when you would output binary data as a string, without converting it to a readable string representation of the binary value (eg. base64).

That type of “magic” behaviour of cfml is one of the very ugly things of ancient cfml: Booleans are in many cases also treated as strings by cfml devs and I have very often seen them even setting a boolean variable like: foo = "yes" or bar = "true".If you want to output a dateObject as a string, convert it using the functions for output (e.g. dateFormat())

1 Like

Thanks for the answer.
I understand that CF is doing something strange, but I’ve a quite big code base to migrate from CF to Lucee, and it’s not very easy to detect such cases (the query can be dynamic). Is there a way (ideally at ‘build time’ or a throw at runtime) to be sure that I do not forgot code to modify?