Skip to main content
R
revdave
Inspiring
October 16, 2012
Answered

fixing mysql database

  • October 16, 2012
  • 1 reply
  • 2225 views

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?

    This topic has been closed for replies.
    Correct answer BKBK

    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.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    October 16, 2012

    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.

    R
    revdaveAuthor
    Inspiring
    October 16, 2012

    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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    October 16, 2012

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices