Insert null into Datetime field

Question

I have a DATETIME column(Default is NULL) in MySQL and trying to insert Empty or NULL value. But I am getting "Incorrect datetime value: '' " error message. If I insert 'NULL' then I am getting "Incorrect datetime value: 'NULL'" error message. How can I insert a blank or NULL value in this column? Thank you for any suggestions.

Here is the code.

        if (empty($_POST["date_field"]))
        {      
          $Date1 = 'NULL';
        }
        else
        {
           $Date1 = strtotime($_POST["date_field"]);
           $Date1 = date("Y-m-d H:i:s", $Date1);
        }

   INSERT INTO Table1(date_field) VALUES('" .$Date1. "');

possible duplicate of [How to store NULL values in datetime fields in MySQL?](http://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql) — Jim, Feb 06 '12 at 21:34

score 9 · Accepted Answer · answered Feb 06 '12 at 21:30

Your problem here is that you're inserting 'NULL' surrounded in quotes, which makes it a string. Instead you need the bare NULL

 if (empty($_POST["date_field"]))
 {      
   $Date1 = NULL;
 }
 else
 {
   $Date1 = strtotime($_POST["date_field"]);
   $Date1 = date("Y-m-d H:i:s", $Date1);
 }

// Surround it in quotes if it isn't NULL.
if ($Date1 === NULL) {
  // Make a string NULL with no extra quotes
  $Date1 = 'NULL';
}
// For non-null values, surround the existing value in quotes...
else $Date1 = "'$Date1'";

// Later, inside your query don't use any additional quotes since you've already quoted it...
INSERT INTO Table1(date_field) VALUES($Date1);

score 0 · Answer 2 · answered Feb 06 '12 at 21:30

Set it to Null without the quotes:

if (empty($_POST["date_field"]))
    {      
      $Date1 = NULL;
    }
    else
    {
       $Date1 = strtotime($_POST["date_field"]);
       $Date1 = date("Y-m-d H:i:s", $Date1);
    }

 INSERT INTO Table1(date_field) VALUES(" .$Date1. ");

And that should work.

score 0 · Answer 3 · answered Feb 06 '12 at 21:32

0

You can insert the primary key field value & any other value leaving your date field and mysql will insert null on its own since its default value

answered Feb 06 '12 at 21:32

Bhrugesh Patel

1,096
5
20
38

Insert null into Datetime field

3 Answers3