Problem with an extra .0 added to Datetime field

[andrewdixon]

Hi.

We have just installed a new W2k3 server with CFMX7 and MySQL 4.0.21 and moved
an application from an older W2k server with CF4.5 and MySQL 4.0.12. Everything
works fine expect one thing. It would appear that whenever we exact a datetime
from a datetime field in the database an extra .0 is added to the end of the
date, for example:

2005-05-05 09:58:33

becomes

2005-05-05 09:58:33.0

This then causes a problem if we try and just any date functions, giving us
the message:

"2005-05-05 09:58:33.0" is an invalid date format.

Anyone else had this? Is it a CFMX7 or MySQL issue? Is there a solution?

[Dan Bracuk]

try dateformat(left(yourfield), 10)

Might work, might not.

[sdupre]

I think I ran into this bug.

The mySQL driver built-in to ColdFusion is 3.2 or so and is very old. Use the one from [url]www.mysql.com[/url]. It should fix this problem.

Stephen Dupre
Macromedia QA

[andrewdixon]

Thanks Stephen, can you give instructions, or point me the direction of
instructions on how to use the driver from the MySQL website?

Also, doesn't this count as a CF bug if it that the driver in CF is simply out
of date, I mean CFMX7 is a new product and as such should surely come with the
most up-to-date drivers. Is there a hotfix on the way for this?

[andrewdixon]

Ok, I have found instruction on how to use the JDBC driver, however this made
no difference still have the same issue. I also tried the MySQL ODBC driver but
I get the same issue with this as well. Maybe MySQL have changed the format of
dates!!! Any other ideas?

[andrewdixon]

I have done a little more digging about and it would appear this is a MySQL/W2k3 issue. See [url]http://bugs.mysql.com/bug.php?id=12520&thanks=3&notify=67[/url] for more information.