MySQL Date issue

[petron2002]

Anyone else have this issue?

I've got a query that pulls dates out of a MySQL db, and often times the date
fields have "0000-00-00" in the date field.

And I get this message "Value '0000-00-00' can not be represented as
java.sql.Date" when trying to run the query.

What's up with that? How can I get around that?

I've got an idea
IF(thisDate='0000-00-00','',thisDate) as thisDate

But I'd like my code to be a little cleaner. Any ideas what's wrong? Is this a
CFMX 7 bug or my bug.

[Dan Bracuk]

At what point does the error occur? Is the datefield in the select clause? I
don't work with mySql, but with the databases I do work with, if it ain't a
valid value, it don't get stored in the first place.

[petron2002]

Dan,
The error occurs when I try to run the cfm from the server. Yea, the field is
in a SELECT inside a cfquery.

I use the date like that in the db because I've had some problems in the past
with NULL and no value fields. And so I have MySQL automatically create the
"0000-00-00" by default when a record is inserted.

[Kronin555]

petron2002,

Try a google search on your error:

[url]http://www.google.com/search?hl=en&q=Value+%270000-00-00%27+can+not+be+represent[/url]
ed+as+java.sql.Date&btnG=Google+Search

Here's 2 good threads from that search:
[url]http://lists.mysql.org/java/8210[/url]
[url]http://forum.java.sun.com/thread.jspa?threadID=649520&messageID=3822351[/url]

Especially of interest is the last post on the 2nd link:
"I have just had the same problem when update Mysql from 4.0 to 4.1 and jdbc
connector from 3.0 to 3.1.7.

I have solved it adding to the connect url:

?zeroDateTimeBehavior=convertToNull

More info here:

http://dev.mysql.com/doc/mysql/en/cj-upgrading.html#cj-upgrading-3-0-to-3-1"

[DROdio.]

YES this is the answer; this just happened to me when i changed my data source
using the J connector.

To make a bit more clear what the last post is saying, in the "JDBC URL" just
add this string "?zeroDateTimeBehavior=convertToNull" to the end of the URL.

so instead of:

jdbc:mysql://SERVER.DOMAIN.com:3306/database_name

you get


jdbc:mysql://SERVER.DOMAIN.com:3306/database_name?zeroDateTimeBehavior=convertTo
Null

it might help to read this first if you're having trouble connecting with MySQL

[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253[/url]