How to store NULL values in datetime fields in MySQL?

Question

I have a "bill_date" field that I want to be blank (NULL) until it's been billed, at which point the date will be entered.

I see that MySQL does not like NULL values in datetime fields. Do any of you have a simple way to handle this, or am I forced to use the min date as a "NULL equivalent" and then check for that date?

Thanks.

EDITED TO ADD:

Ok I do see that MySQL will accept the NULL value, but it won't accept it as a database update if I'm updating the record using PHP.

The variable name is $bill_date but it won't leave the variable as NULL if I update a record without sending a value to $bill_date -- I get this error:

Database query failed: Incorrect datetime value: '' for column 'bill_date' at row 1

I assume I need to actually send the word NULL, or leave it out of the update query altogether, to avoid this error? Am I right? Thanks!!!

Answer 1

MySQL does allow NULL values for datetime fields. I just tested it:

mysql> create table datetimetest (testcolumn datetime null default null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into datetimetest (testcolumn) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetimetest;
+------------+
| testcolumn |
+------------+
| NULL       | 
+------------+
1 row in set (0.00 sec)

I'm using this version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.45    | 
+-----------+
1 row in set (0.03 sec)

EDIT #1: I see in your edit that the error message you are getting in PHP indicates that you are passing an empty string (i.e. ''), not null. An empty string is different than null and is not a valid datetime value which is why you are getting that error message. You must pass the special sql keyword null if that's what you mean. Also, don't put any quotes around the word null. See my insert statement above for an example of how to insert null.

EDIT #2: Are you using PDO? If so, when you bind your null param, make sure to use the [PDO::PARAM_NULL][1] type when binding a null. See the answer to this stackoverflow question on how to properly insert null using PDO.

Answer 2

I just tested in MySQL v5.0.6 and the datetime column accepted null without issue.

Answer 3

It depends on how you declare your table. NULL would not be allowed in:

create table MyTable (col1 datetime NOT NULL);

But it would be allowed in:

create table MyTable (col1 datetime NULL);

The second is the default, so someone must've actively decided that the column should not be nullable.