Coldfusion 21. Datetime value retrieved from a MySQL8 database error

[Imidi]

Hello everyone!
I ran into the next datetime display issue. 
Retrieved from a MySQL8 database, the datetime: 2022-04-06 19:27:24, trigger the following error_:
“2022-04-06T19: 28: 53 is an invalid date format.” 
However this is correct in javascript!

[Charlie Arehart]

See if this is resolved by a bug fix offered by Adobe at the ticket (and as discussed in my last couple of comments there):

https://tracker.adobe.com/#/view/CF-4211276

Please let us know here if it helps, or not. 

[Imidi]

Hi Charlie!
Unfortunately, the issue is not resolved.The problem remains with "mysql-connector-java-8.0.28"! The result of "cfdump" regarding text data in the database is also interesting.Best regards,Silviu.

[Charlie Arehart]

Please confirm what steps you took. 

[BKBK · Accepted solution]

Hi @Imidi ,

What you observe is unlikely to be a bug. The likely explanation is that your code contains some ambiguity:

1. You say the date is 2022-04-06 19:27:24, but it is not. ColdFusion tells you it has evaluated qActivitate.Datetime to be 2022-04-06T19:27:24, which is a date expressed in ISO 8601 UTC format, minus the time-zone part. These two dates are different. Especially when you take point 2. into account.
2.  Your function, 
   

   LSdateformat(qActivitate.Datetime)​

   is a locale-based function. Yet it fails to mention the mask and the locale. ColdFusion is therefore forced to use default values for mask and locale, which may be incompatible with the value 2022-04-06T19:27:24. For details, see the LSDateFormat documentation.

3. It is doubtful whether the variable qActivitate.Datetime is a date object. As you can see in the documentation, the first argument of the DateFormat/LSDateformat function should, preferably, be a date object.
    
   To avoid the error, you could try the following alternatives:
   

   // Create a date object earlier in the code.
   <cfset datetimeObject=parseDateTime(qActivitate.Datetime)>
   
   // Subsequently...
   
   // Using an explicit Locale (Spain/Spanish, for example) 
   lsdateformat(date=datetimeObject, mask="yyyy-mm-dd", locale="es_ES")
   
   // Using the locale with which the ColdFusion server is configured
   lsdateformat(date=datetimeObject, mask="yyyy-mm-dd")
   
   // Avoiding specifying the locale
   dateformat(date=datetimeObject, mask="yyyy-mm-dd")​

[Imidi]

Thanks BKBK!
Your code is a solution. 
The problem is with the inconsistency between Coldfusion10 and the following.
The text: "2022-04-06 19:27:24" from the base date is indeed: "2022-04-06T19: 27: 24" , 
but the format in cfinput value: 
"#LSdateformat (my) # #LStimeformat (my, ' HH: mm: ss') # "
should have reproduced the text in the database as Coldfusion 10 does!
They managed to make, the commonplace "cfdump", unusable when it comes to DateTime.
As an amateur, I play Coldfusion to keep my brain healthy! 
I don't want to think about the problems a network administrator faces ...
Thanks again and good luck.

[BKBK]

As an amateur, I play Coldfusion to keep my brain healthy! 

By Imidi

Thanks to you, too.

That is one of the most poetic things I've heard anyone say about ColdFusion. 🙂