List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 1 1999 1:05pm
Subject:Re: Verifying dates
View as plain text  
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
date.

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
  U.D.F. Registry
  <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).

Regards,
  Martin
-- 
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
Thread
Verifying datesPara-dox1 Aug
  • Re: Verifying datesMartin Ramsch1 Aug
  • Verifying datessinisa1 Aug