MySQL date format

  • Thread starter Thread starter jimmy
  • Start date Start date
J

jimmy

I'm trying to insert a date into a MySQL date column. The string i am
trying to insert takes the following format:

2007-02-23

which corresponds to the date format that MySQL uses which is YYYY-MM-
DD. When i insert this into the database through an INSERT query
however the date field uses the value 0000-00-00. Why might this be?

Thanks

James
 
jimmy said:
I'm trying to insert a date into a MySQL date column. The string i am
trying to insert takes the following format:

2007-02-23

which corresponds to the date format that MySQL uses which is YYYY-MM-
DD. When i insert this into the database through an INSERT query
however the date field uses the value 0000-00-00. Why might this be?

Thanks

James
Click to expand...

When MySQL fails to parse a date value, it silently inserts a zero value
instead. (I just can not fathom the reasoning behind the choise to just
swallow an error without issuing an error message...)

The string that you are using has the ISO 8601 format, which is good, as
it's unambigous. I don't know why the database does not parse it
correctly, as it really should. You haven't forgotten to put apostrophes
around the value or something like that?

The solution is to use a parameterised query. Then the database driver
gets to worry about what date format to use. Turn the value into a
DateTime value before you put it into the parameter.
 
Back
Top