Warning: # 1265 Data truncated for column 'pdd' in row 1

Question

I am having trouble setting the PDD (patient's death date) to null on PHPMYADMIN until such a date of death occurs; also on the client side, then I can check the NULL data to use it.

Can someone suggest me a solution please?

 patientnhs_no hospital_no sex name surname dob address pls pdd 1001001001 6000001 m john smith 1941-01-01 Bournmouth 1 0000-00-00 

(PDD must be null if it is alive or dead, if dead)

Answer 1

As the message says, you need to increase the length of the column so that it matches the length of the data you are trying to insert ( 0000-00-00)

EDIT 1 :

Following your comment, I run a test pattern:

 mysql> create table testDate(id int(2) not null auto_increment, pdd date default null, primary key(id)); Query OK, 0 rows affected (0.20 sec) 

Insert:

 mysql> insert into testDate values(1,'0000-00-00'); Query OK, 1 row affected (0.06 sec) 

EDIT 2:

So, maybe you want to insert a NULL value in the pdd field as indicated in your comment? You can do this in two ways:

Method 1:

 mysql> insert into testDate values(2,''); Query OK, 1 row affected, 1 warning (0.06 sec) 

Method 2:

 mysql> insert into testDate values(3,NULL); Query OK, 1 row affected (0.07 sec) 

EDIT 3:

Failed to change the default value for the pdd field. Here is the syntax how to do it (in my case, I set it to NULL at the beginning, now I will change it to NOT NULL)

 mysql> alter table testDate modify pdd date not null; Query OK, 3 rows affected, 1 warning (0.60 sec) Records: 3 Duplicates: 0 Warnings: 1 

Answer 2

Most likely, you will enter the string 'NULL' into the table, and not the actual NULL , but there may be other things, illustration:

 mysql> CREATE TABLE date_test (pdd DATE NOT NULL); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO date_test VALUES (NULL); ERROR 1048 (23000): Column 'pdd' cannot be null mysql> INSERT INTO date_test VALUES ('NULL'); Query OK, 1 row affected, 1 warning (0.05 sec) mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'pdd' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM date_test; +------------+ | pdd | +------------+ | 0000-00-00 | +------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE date_test MODIFY COLUMN pdd DATE NULL; Query OK, 1 row affected (0.15 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO date_test VALUES (NULL); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM date_test; +------------+ | pdd | +------------+ | 0000-00-00 | | NULL | +------------+ 2 rows in set (0.00 sec)