Inserting a default null date

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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Inserting a default null date

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
>
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Inserting a default null date

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?

That's silly, if the data you want to insert is considered to be invalid,
reject it,
don't replace it with NULL.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download FREE! Database Workbench Lite for MySQL!


Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Inserting a default null date

Agreed. And don't forget to listen to the warnings MySQL sends back,
e.g.:

mysql> create table temp_date(d date default null);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into temp_date(d) values('2009-13-99');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 |=20
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from temp_date;
+------------+
| d |
+------------+
| 0000-00-00 |=20
+------------+
1 row in set (0.01 sec)

Andrew

-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@upscene.com]=20
Sent: 15 May 2009 14:43
To: mysql@lists.mysql.com
Subject: Re: Inserting a default null date=20

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=20
> should).
>
> But if I enter an invalid date by mistake, the date 0000-00-00 date
date=20
> 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?

That's silly, if the data you want to insert is considered to be
invalid,=20
reject it,
don't replace it with NULL.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download FREE! Database Workbench Lite for MySQL!


Database questions? Check the forum:
http://www.databasedevelopmentforum.com=20


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dandrew.braithwaite@love film.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg