On So, 1999-08-01 08:44:53 -0400,
David Meyer <paradox@stripped> wrote:
> How can I tell MySQL that if the date specified in an INSERT/UPDATE is
> invalid (like '9999-99-99') that I want NULL inserted?
Basic answer is, you can't directly with MySQL but have to do this
kind of type checking on the application side before you insert the
A work-around is to use FROM_DAYS(TO_DAYS(date)), that is convert the
date into number of days and back again. If the result equals the
original date, it should have been okay.
This gives you a test like this:
IF(date=FROM_DAYS(TO_DAYS(date)), date, NULL)
But, have a look at the
<URL: http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ >
where you'll find an UDF called "valid_date" that is supposed
to do exactly such checks.
I'm just going to install this myself so I can't give no further help
so far (in regards to comiling/installation of this beast).
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7