TIMESTAMP issues moving from MS SQL to MySQL

[revdave]

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!

[revdave]

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]

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