List:General Discussion« Previous MessageNext Message »
From:Janek Bogucki Date:May 15 2009 9:34am
Subject:Re: Inserting a default null date
View as plain text  
Hi Octavian,

One approach is to use a trigger,

mysql> set sql_mode = '';

mysql> create table temp_date(d date default null);

mysql> create trigger temp_date_bi before insert on temp_date for each row set new.d =
> if(new.d = '0000-00-00', null, new.d);

mysql> insert into temp_date(d) values('2009-13-99');

mysql> select * from temp_date;
+------+
| d    |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

mysql> insert into temp_date(d) values('2009-11-19');

mysql> select * from temp_date;
+------------+
| d          |
+------------+
| NULL       |
| 2009-11-19 |
+------------+
2 rows in set (0.07 sec)

Cheers,
-Janek
CMDEV 5.0

On Fri, 2009-05-15 at 11:32 +0300, Octavian Rasnita wrote:
> Hi,
> 
> I have a table with a column like:
> 
> date date default null,
> 
> If I enter an empty string in it, the default null value is added (as it 
> should).
> 
> But if I enter an invalid date by mistake, the date 0000-00-00 date date is 
> entered instead of the default null, and this is not good.
> 
> Can I do something to force MySQL to insert a null date if the entered date 
> is an invalid one?
> 
> Thank you.
> 
> --
> Octavian
> 
> 
> 
> 

Thread
Inserting a default null date Octavian Rasnita15 May
  • Re: Inserting a default null dateJanek Bogucki15 May
  • Re: Inserting a default null date Martijn Tonies15 May
    • RE: Inserting a default null date Andrew Braithwaite15 May