MySQL Dates

Forum|Forum|15 years ago
June 26, 2010
3 replies
809 views

I am currently converting a MS SQL to MySQL and are having a bit of trouble with the way dates are stored in the database. I do not know if this is a mysql question or cf question but I thought I would start here.

The is a field called birthday. if someone decides not to fill it in the date in the database is - 0000-00-00 00:00:00. When i do a query on that table I get an error that says the following:

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

I have tried to change the default value to 0 but it still saves the date the same way. What can i do to stop getting this error.

thanks in advance.

This topic has been closed for replies.

A

Adam Cameron.

Inspiring

It sounds like you're being caught out by the situation detailed here:

http://www.bennadel.com/blog/1434-MySQL-Cannot-Convert-Value-0000-00-00-00-00-00-From-Column-XX-To-TIMESTAMP.htm

Hint for solving this sort of issue yourself: google the error message. I googled "Cannot convert value '0000-00-00 00:00:00' from column 8 to TIMESTAMP" and the answer was the first match.

It's usually safe to assume that will almost never be the first person to encounter a given problem, Google will probably be able to turn up an answer for you.

--

Adam

J

jjsand28Author

Inspiring

I tried searching for that but must have used the wrong query. It works on the local development server I have here. Let's hope it works on the live server. But by that point all the old data should be in the new datbase anyway and it won't be an issue anymore. Creating new records does not bring up the error.

Thanks everyone for your guidance.

ilssac

Inspiring

That would be my preference as well. Make the data a null if a user does not provide a value.

Some people don't like nulls, but they make sense to me. Zero is often a poor choice for "no value" because it is a ligitimate value.

But to answer the other part of your question. The database is storing all those zeros (or actually the date-time value that is being displayed as all those zeros) because the field is a date-time field.