How to store NULL values in datetime fields in MySQL?

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 v开发者_JS百科ariable 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!!!

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)

Speaking of inserting NULL values through PHP code, given you are using prepared statements (as you definitely should), there is no problem with inserting NULL values. Just bind your variable the usual way, and all PHP variables that contain null values will be stored as NULL in MySQL.

Just make sure that your PHP variable indeed contains NULL and not an empty string.

This is a a sensible point.

A null date is not a zero date. They may look the same, but they ain't. In mysql, a null date value is null. A zero date value is an empty string ('') and '0000-00-00 00:00:00'

On a null date "... where mydate = ''" will fail.
On an empty/zero date "... where mydate is null" will fail.

But now let's get funky. In mysql dates, empty/zero date are strictly the same.

by example

select if(myDate is null, 'null', myDate) as mydate from myTable where  myDate = '';
select if(myDate is null, 'null', myDate) as mydate from myTable where  myDate = '0000-00-00 00:00:00'

will BOTH output: '0000-00-00 00:00:00'. if you update myDate with '' or '0000-00-00 00:00:00', both selects will still work the same.

In php, the mysql null dates type will be respected with the standard mysql connector, and be real nulls ($var === null, is_null($var)). Empty dates will always be represented as '0000-00-00 00:00:00'.

I strongly advise to use only null dates, OR only empty dates if you can. (some systems will use "virual" zero dates which are valid Gregorian dates, like 1970-01-01 (linux) or 0001-01-01 (oracle).

empty dates are easier in php/mysql. You don't have the "where field is null" to handle. However, you have to "manually" transform the '0000-00-00 00:00:00' date in '' to display empty fields. (to store or search you don't have special case to handle for zero dates, which is nice).

Null dates need better care. you have to be careful when you insert or update to NOT add quotes around null, else a zero date will be inserted instead of null, which causes your standard data havoc. In search forms, you will need to handle cases like "and mydate is not null", and so on.

Null dates are usually more work. but they much MUCH MUCH faster than zero dates for queries.

I had this problem on windows.

This is the solution:

To pass '' for NULL you should disable STRICT_MODE (which is enabled by default on Windows installations)

BTW It's funny to pass '' for NULL. I don't know why they let this kind of behavior.

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.

If your datetime column is not accepting null values it might be not nullable, so you can update the column using this command:

ALTER TABLE your_table modify your_coloumn datetime null

And when you want to save a null field, you can leave it alone (not assigning anything to that field), or you can set it to Null (instead of '')

For what it is worth: I was experiencing a similar issue trying to update a MySQL table via Perl. The update would fail when an empty string value (translated from a null value from a read from another platform) was passed to the date column ('dtcol' in the code sample below). I was finally successful getting the data updated by using an IF statement embedded in my update statement:

    ...
    my $stmnt='update tbl set colA=?,dtcol=if(?="",null,?) where colC=?';
    my $status=$dbh->do($stmt,undef,$iref[1],$iref[2],$iref[2],$ref[0]);
    ...

I just discovered that MySQL will take null provided the default for the field is null and I write specific if statements and leave off the quotes. This works for update as well.

if(empty($odate) AND empty($ddate))
{
  $query2="UPDATE items SET odate=null, ddate=null, istatus='$istatus' WHERE id='$id' ";
};

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

Specifically relating to the error you're getting, you can't do something like this in PHP for a nullable field in MySQL:

$sql = 'INSERT INTO table (col1, col2) VALUES(' . $col1 . ', ' . null . ')';

Because null in PHP will equate to an empty string which is not the same as a NULL value in MysQL. Instead you want to do this:

$sql = 'INSERT INTO table (col1, col2) VALUES(' . $col1 . ', ' . (is_null($col2) ? 'NULL' : $col2). ')';

Of course you don't have to use is_null but I figure that it demonstrates the point a little better. Probably safer to use empty() or something like that. And if $col2 happens to be a string which you would enclose in double quotes in the query, don't forget not to include those around the 'NULL' string, otherwise it wont work.

Hope that helps!