Skip to main content
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Josh Knutson and Ryan Vikander
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Josh Knutson@Emergence ) and Ryan Vikander@rvikander )

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

By on
Tags:

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.

Reader Comments

1 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,331 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,331 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!

2 Comments

Posting another solution would be to CONVERT(theDateField, DATE) in the SELECT statement too (assuming you're not doing a SELECT * ....

15,331 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. ๐Ÿ‘

414 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. โค๏ธ

15,331 Comments

@Charles,

Boom! ๐Ÿ’ฅ My brain is just getting going on this fine Sunday. Caffeine hasn't kicked in yet.

Post A Comment — I'd Love To Hear From You!

Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.