After setting up ColdFusion 2021 I found that when ParseDateTime is being used to format a datetime value from SQL Server it won't format the date as an ODBC literal like ColdFusion 2016 does, e.g. {ts '2021-05-15 13:20:51'}. Instead it just outputs the value unchanged from the database.
Here is my environment:
Coldfusion 2016 Server:
Version: 2016,0,17,325979
Tomcat Version: 8.5.61.0
Edition: Developer
Operating System: Windows 7
Coldfusion 2021 Server:
Version: 2021,0,01,325996
Tomcat Version: 9.0.41.0
Edition: Developer
Operating System: Windows 10
The database is SQL Server 2008 R2 and ColdFusion datasource is using the MS SQL Server driver.
An example query:
<cfquery name="qryDates">
select id, expiry_date
from purchases
</cfquery>
<cfoutput>#ParseDateTime(qryDates.expiry_date)#</cfoutput>
Coldfusion 2016 output:
{ts '2021-05-15 13:20:51'}
Coldfusion 2021 output:
2021-05-15 13:20:51.0
Is there a way to get ParseDateTime to behave the way it does on my CF 2016 and every other CF server I have used in the past? I do not want to have to change the formatting to accomplish this (e.g. #DateTimeFormat(ParseDateTime(qryDates.expiry_date))#).
Thank you for any advice.
I don't have any way to test my assumptions right now, but I assume you're seeing the side effects of the way ParseDateTime() handles a Java object, and the differences of that function between CF2016 and CF2021/18.
Since expriry_date is an actual datetime object in your database, cfquery from the database will give you a sql object instead of just a string that looks like a date. Since it has both date and time components, I believe that JDBC will send a java.sql.Timestamp object back to CF.
The purpose of ParseDateTime() is for converting a string to a date object, and even though it can "format" a date object, that's not what it's intended for. It's initial assumption is that it's being given a string, and because CF is dynamically typed, it will usually try to implicitly convert a value into the correct type. And since the database value is date-ish, CF will try to make it appropriately look like a string. At least that's the way it was intended to work. And it looks like that is what is happening in <CF2016 and Lucee, and the value is getting a "{ts...".
It appears that CF2021/18 is receiving a date object and not really doing much with it. It seems to be just passing the Java object (without the "{ts...") back out, and when ColdFusion tries to convert it to a string, it's not a ColdFusion timestamp variable so there is no "{ts...".
DEMO CODE:
<cfscript>
/*** CREATE QUERY OBJECT ***/
theDateQuery = QueryNew("dt","timestamp",[{"dt":createODBCDateTime('2021-04-09 12:01:02.345')}]) ;
writeDump(theDateQuery);
/*** SET QUERY RESULT TO A VARIABLE ***/
dobj = theDateQuery.dt[1] ;
writeOutput("dobj: " & dobj & " ("& getMetadata(dobj).getName() & ")<br>");
/*** CONVERT QUERY VARIABLE TO A STRING ***/
dstr = theDateQuery.dt[1].toString() ;
writeOutput("dstr: " & dstr & " ("& getMetadata(dstr).getName() & ")<br>");
/*** PARSEDATETIME OF OBJECT ***/
x = parseDateTime(dobj) ;
writeOutput("x: " & x & " ("& getMetadata(x).getName() & ")<br>");
/*** PARSEDATETIME OF STRING ***/
y = parseDateTime(dstr) ;
writeOutput("y: " & y & " ("& getMetadata(y).getName() & ")<br>");
</cfscript>
CF 2016 and lower
https://trycf.com/gist/c9c35dccb04f91cd6c06e4082ed306ca/acf2016?theme=monokai
Your database is giving the ColdFusion query back a Java object, which is going into the ParseDateTime function, and CF is doing its magic then giving you back a ColdFusion date object, which looks like a CF timestamp that is easily coerced into what you want it to display.
dobj: 2021-04-09 12:01:02.345 (java.sql.Timestamp)
x: {ts '2021-04-09 12:01:02'} (coldfusion.runtime.OleDateTime)
CF2021 (and CF2018)
https://trycf.com/gist/4c9bbc036a63a135962f0912b8591e00/acf2021?theme=monokai (trycf link for CF2021 seems to be defaulting to Lucee5, so you'll have to reselect CF2021)
It looks like the behavior changed slightly in CF2018. I don't know the inner workings of the ParseDateTime functions, but I'm assuming that in earlier versions, it would convert the date object to a date string before it converted it into a ColdFusion object on return. As of 2018, it appears that if it receives a Java data object, it recognizes that it's already a date object and just returns it (bypassing a conversion to a ColdFusion date string and then object). This is seems to be more in line with the originally stated behavior. And since the Java timestamp value doesn't have "{ts...", but CF does, it changes the way a string us ultimately created from the function's return value. This is possibly a compatibility bug (since the behavior is subtly different), but I'm not completely sure.
Regardless, it seems to be caused by the simple fact that ParseDateTime is being used here for something other than its intended purpose. Just like using TimeFormat(now(),"yyyy-MMM-dd hh:nn:ss"). ACF will return a string with the Year and Day masked, even though that's not something that TimeFormat() is intended for (NOTE: Lucee seems to handle it correctly). If a function is used in an unexpected manner, it shouldn't be expected to return consistent results in different versions.
And that's where I get kinda iffy on whether it should be a bug or not. In the past, most ColdFusion functions didn't much care if a "date" was a string or an object. It seems that CF2018+ does. Or at least treats them differently.
Ultimately, your code should probably be fixed to use functions the way they were meant to be used. However, I understand that might be a bit much. You might be able to fix your current issue by explicitly converting the query object into a string before you parse it.
ParseDateTime(qryDates.expiry_date.toString())
One other thing to note is that in CF2016, if your datetime value had a millisecond component, that would be stripped off in your final ParseDateTime value. However, in CF2021/18, since the Java value doesn't appear to be changed, the milliseconds component will still be in your return value. toString() should also fix that, and you'll get exactly what you had before.