fixing mysql database

[revdave]

I have an older database that came from MS SQL and was imported long ago to mysql. It seems to have some bad dates that I need to fix...

some appear in the format:

0000-00-00 00:00:00

error in cf search says:

can not be represented as java.sql.Timestamp

(I think they are talking about this field...)

so it's hard to update the db when it keeps erroring out...

also I tried searching with phpmyadmin in the field for: LIKE and 0000

but shows empty query

i noticed that newer TS seem to have an extra .0 at the end like:

0000-00-00 00:00:00 = old problem?

2012-10-01 00:00:00.0 = new ok...

Q: How can I get around this?

[BKBK · Accepted solution]

It is a well-known problem in older versions of MySQL. A common solution is to include the property

zeroDateTimeBehavior=convertToNull

in the Connection String field in the MySQL datasource settings in the ColdFusion Administrator. Queries will then return NULL wherever 0000-00-00 00:00:00 is encountered. See, for example, Ben Nadel's blog on this.

[revdave]

Hi BKBK,

Wow - that worked - I would never have found that - thanks a lot!!!!

After you said that - I checked the hosted server (i have no access) and it was already working but my home dev server did not have the string - so I added it and now all is fine!!!!

Also ...

I was able to query the bad records with...

SELECT * FROM mytable

Where thisdate = '0000-00-00 00:00:00'

so now I can fix them....

Q: I can update the record and put in a real timedate or how would I just set the field to null - what is the syntax for this?

[BKBK]

Remember that you have given MySQL instructions to convert the zero date to NULL. You should therefore write something like

SELECT * FROM mytable

Where thisdate is null

[brilang]

This does not work in CF10.

When you use the zeroDateTimeBehavoir=convertToNull in your connection string in CF10, you will receive this error:

Connection verification failed for data source: pcbcrm

java.sql.SQLException: The connection property 'zeroDateTimeBehavior' only accepts values of the form: 'exception', 'round' or 'convertToNull'. The value 'convertToNull ' is not in this set.

The root cause was that: java.sql.SQLException: The connection property 'zeroDateTimeBehavior' only accepts values of the form: 'exception', 'round' or 'convertToNull'. The value 'convertToNull ' is not in this set.

It has been reported as part of bug here

https://bugbase.adobe.com/index.cfm?event=bug&id=3197321

[revdave]

Hi brilang,

I have - ColdFusion 10 Update 2 and didn't seem to have the error you mentioned.

BTW - after adding zeroDateTimeBehavior=convertToNull - when I search for :

SELECT * FROM mytable

Where thisdate = '0000-00-00 00:00:00'

still works and finds just '0000-00-00 00:00:00' although cfoutput display is blank

SELECT * FROM mytable

Where thisdate is null

these will find the actual records marked null

I can use some review help with MYSQL and coldfusion...

is there a web link that can help differential between query for :

1 - where myfield = ''

2 - where myfield is null

3 - where myfield = 0

Thanks in advance