Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Tim Cunningham
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Tim Cunningham

The Value Class java.time.LocalDateTime Cannot Be Converted To A Date In ColdFusion

By
Published in — Comments (12)

Yesterday, I went to debug an issue with the latest Adobe ColdFusion 2018 Updater. So, I first updated my CommandBox Docker Image (in an effort to reproduce the issue in my local development environment). This sent me down a 4-hour rabbit hole just trying to getting my ColdFusion site up-and-running before I could even debug the originally issue. One of the problems that I ran up against was an incompatibility with Adobe ColdFusion and the latest MySQL Connector/J, version 8.0.23+.

When I updated my CommandBox Docker Image, I went from:

ortussolutions/commandbox:adobe2018-2.7.2

... to the most recently-built version:

ortussolutions/commandbox:adobe2018-3.4.4

This update included a major, breaking change update in the underlying MySQL Connector/J JAR file. My Adobe ColdFusion install went from:

mysql-connector-java-5.1.42-bin.jar

... to:

mysql-connector-java-8.0.25.jar

... which resulted in the following ColdFusion error the when I went to use dateFormat() on one of the dates returned in my CFQuery tag:

The value class java.time.LocalDateTime cannot be converted to a date.

Of course, I had no idea that this was related to the MySQL driver update. In fact, I had no idea what in the heck-fire was going on at all! Thankfully, after about an hour of Googling unsuccessfully for the issue, I found a MySQL bug ticket in which Scott Jibben mentioned that the latest MySQL Connector/J version was breaking ColdFusion:

This change breaks the MySQL implementation in Adobe ColdFusion.

For query resultsets, a java.time.LocalDateTime object is returned instead of a datetime string.

Reverting to 8.0.22 fixed this problem.

This is when I checked in the Adobe ColdFusion Admin and saw that I was on 8.0.25. I needed to downgrade the MySQL driver. I didn't really know what the best was to do this was, using Docker and CommandBox. But, I ended up downloading the 8.0.22 Connector/J version, and then creating a Dockerfile that added it to the Image while removing the newer version:

FROM ortussolutions/commandbox:adobe2018-3.4.4

# The core image seems to break if APP_DIR is using wwwroot.
# --
# https://github.com/Ortus-Solutions/docker-commandbox/issues/55
RUN box config set server.singleServerMode=false

# Downgrade MySQL Connector/J to fix LocalDateTime casting issue.
ADD ./mysql-connector-java-8.0.22.jar /usr/local/lib/serverHome/WEB-INF/cfusion/lib/
RUN rm /usr/local/lib/serverHome/WEB-INF/cfusion/lib/mysql-connector-java-8.0.25.jar

This seems to have fixed my local development environment for Adobe ColdFusion 2018, updater 3 using the latest CommandBox Docker Image.

ASIDE: You'll see that my Dockerfile also includes a CommandBox config setting to disable the singleServerMode. This was also something that broke in the latest CommandBox image. The fix I applied was suggested in a CommandBox issue on GitHub.

Once I had a better understanding of what was going on, I did some better Googling and came across more information on the issue:

I'm not sure why these articles didn't come up in my original search?

The whole adventure served to remind me how little I actually know about Docker. And about getting the core server stuff working. So, this was a "nice" reality check. I assume that the future Adobe ColdFusion Updater will either fix the underlying issue; or, will downgrade the MySQL Connector/J. In any case, at least I have my local development environment up-and-running (and can get back to debugging the original Updater 3 issue).

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4144

Reader Comments

4 Comments

Thanks for the info, I just ran into this issue myself! Unfortunately, I didn't find your article until after fixing it, but it did lead me to another solution that doesn't mess with the connector. If you use 'parsedatetime' : example - #dateformat(parsedatetime(yourdatetimevar),'mm/dd/yyyy')# - it works. It works with other date functions as well. I'm not concerned about multiple time zones, so I don't know if that would have an impact, but it worked for my needs.

15,902 Comments

@Stuart,

Oh, interesting! I wonder why that works - maybe it implicitly converts the date to String before then parsing it into a ColdFusion date/time value. All in all, just frustrating stuff. I hope Adobe fixes it soon!

4 Comments

Hi Ben

Just wanted to thank you for this post - I ran in this issue when upgrading to CF2021 on my dev machine. Thought I was going to have to convert all datetime columns to timestamps (ugh) but using 8.0.22 connector .jar also worked for me.

Shame that 6 months later and Adobe still haven't fixed this as this means we're having to use a connector version that is now quite old (current v is 8.0.29)

15,902 Comments

@Nick,

Awesome! Glad this helped out. Yeah, seems like such a strange breaking change in the driver. Or the runtime? I'm not really sure where the problem is 🤪 I just want to query my data and get on with my app!

15,902 Comments

@Steve,

Oh that's good to know. That could work in a pinch if it wasn't an entire application breaking, but just a small script something. 👍

449 Comments

Hi Ben

I am getting this issue after a migration for a client from CF2018.02 to CF2021.
I have just managed to secure a new Coldfusion contract after 3 years of pure Angular development.
Coldfusion projects in the UK are rarer than hen's teeth. 😕
I am over the moon.🙂

Anyway, just a little more information, for those that cannot be bothered to engage their brain on a Sunday 😀

With the link you have posted:

Connector/J 8.0.30

For Windows users, I then clicked on Archives and then:

Product Version = 8.0.22
Operating system = platform independent

Then stop any CommandBox servers that are running.
After unzipping the download, I then placed the following:

mysql-connector-java-8.0.22/mysql-connector-java-8.0.22/mysql-connector-java-8.0.22.jar

Into:

C:\Users[userName].CommandBox\server[installationId]-installer\adobe-2021.0.04.330004\WEB-INF\cfusion\lib

Then restart your server and voila, it should work again!

Anyway, thanks for this very helpful blog as always. ❤️

1 Comments

@Ben,

I tried this, it initially worked, but now getting this error:

is not indexable by CONVERT(EXPIRATION, type: java.lang.IllegalArgumentException

15,902 Comments

@Vahe,

Sorry, I am not sure what that error means. Given that you are seeing a Java error (and not a MySQL error), it makes me think that you're executing CONVERT(EXPIRATION in the ColdFusion context, and not in the SQL context. But, I don't understand how that would be possible.

1 Comments

@Charles

Thank you so much! I'm on ColdFusion 11 and switching the .jar file from 8.0.33 to 8.0.22 as you described fixed the problem for me.

@Stuart - your solution did work for me as well, before the switch, but would have involved a lot of recoding. Great solution for those unable to go backward.

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel