How to store NULL values in datetime fields in MyS

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

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.

Answer 2

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 3

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.

Answer 4

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' ";
};

Answer 5

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!

Answer 6

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]);
    ...