Way to store dates with missing days/months in mysql (php)

views:

answers:

+1 Q:

Way to store dates with missing days/months in mysql (php)

I want to store dates in mysql so I set the table to be of type 'Date', which is fine but mysql requires that the full date is provided YYYY-MM-DD.

However some of my data does not include day and some is missing the month. Now I could just use a varchar(10) field, but then its difficult to run date based queries on the data.

Is there another date format which is not as strong and would allow me to use ?? or 00 where the day/month is not known?

+2 A:

You can replace with zeros.

Babiker 2010-06-09 22:20:36

+2 A:

Following from Babiker's answer, this is what the documentation says:

Ranges for the month and day specifiers begin with zero due to the fact that MySQL allows the storing of incomplete dates such as '2014-00-00'.

Daniel Vassallo 2010-06-09 22:24:45

I have been through this page http://dev.mysql.com/doc/refman/5.1/en/datetime.html and it says that any non standard date is completely replaced with zeros so 2014-00-00 will become 0000-00-00 the day and month have to be valid. But i tried replacing with 00 and it worked with direct entry, i think somewhere along the line the php must have been blocking the 00 date and months!

Paul M 2010-06-09 22:57:59

Actually we were passing in ?? as the unknown date and so the DB truncated the field to 0000-00-00, but if we substitute ?? for 00 in either the day or month it should work.Many thanks both!

Paul M 2010-06-09 22:59:52

@Paul: The "illegal" dates that [the article](http://dev.mysql.com/doc/refman/5.1/en/datetime.html) refers to are dates like `'2010-13-00'` or `'2010-12-32'. In that case, these illegal dates are coverted to `0000-00-00`.

Daniel Vassallo 2010-06-09 23:00:29

ansaurus

tags:

views:

answers:

Way to store dates with missing days/months in mysql (php)

related questions