Skip to main content
R
revdave
Inspiring
October 24, 2009
Question

TIMESTAMP issues moving from MS SQL to MySQL

  • October 24, 2009
  • 1 reply
  • 745 views

Hi Folks, I'm having TIMESTAMP issues moving from MS SQL to MySQL 5


i got the schema and data moved from MS sql to MySQl 5



- the date field in my sql table is set to datetime

- the content is like:

2004-02-05 14:31:00

but looks like:

2004-02-05 14:31:00.0 (extra .0) - when displayed raw in CF 8 on a page


- when I query the table, I get this error:


Error Executing Database Query.


Cannot convert value '0000-00-00 00:00:00' from column 24 to TIMESTAMP.            


- I tried to use the type timestamp instead but that did not help?


Q: How do I fix this error?


Thanks in advance!

    This topic has been closed for replies.

    1 reply

    R
    revdaveAuthor
    Inspiring
    October 25, 2009

    I see that it is an issue with fields with literal content: '0000-00-00 00:00:00'

    So what is the best way to store default empty data? as NULL or as choice = "none"

    this is for cf 8 and mysql5.xx

    http://www.bennadel.com/blog/1434-mysql-cannot-convert-value-0000-00-00-00-00-00-from-column-xx-to-timestamp.htmBTW:

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    October 25, 2009
    So what is the best way to store default empty data? as NULL or as choice = "none"

    I would convert the zero date to null. That is, I would use the connection-string zeroDateTimeBehavior=convertToNull. That has at least one advantage, namely, backward compatibility with older MySQL drivers which interpreted a zero date as null. See the technote on zero MySQL dates

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