Mysql, How to Insert Null Dates

How to store NULL values in datetime fields in MySQL?

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.

Unable to insert NULL in date field for MySQL

The default value for your column is NULL, so it doesn't have to be in your INSERT statement.

$query_string = "INSERT INTO inward_credit
     (custi_id_fk, driver_id_fk, today_date, amount, mode, cheque_no)
     VALUES
     ('$cust_id1', '$driver_id1', '$today_date', '$credit_collected', '$mode', '$cheque_number')";

     $insert_credit = mysqli_query($con, $query_string);

Edit

If you are wanting to insert NULL, use the following;

• Don't quote the variable $chq_date, as that will make NULL a string. See Marc B's Answer

//if/else logic.
if(<<what>>) {
  $chq_date = "2014/09/09";
} else {
  $chq_date = "NULL";
}

$query_string = "INSERT INTO inward_credit
     (custi_id_fk, driver_id_fk, today_date, amount, mode, cheque_date, cheque_no)
     VALUES
     ('$cust_id1', '$driver_id1', '$today_date', '$credit_collected', '$mode', $chq_date, '$cheque_number')";

     $insert_credit = mysqli_query($con, $query_string);

Mysql - inserting NULL values in datetime field

Send an unquoted literal NULL to MySQL. MySQL does not know of your PHP's NULL type.

UPDATE tbl_dummy SET Id=3,dtp=NULL,Remark="update1"

How to import datetime field coded with `NULL` keyword in MySQL?

In MySQL version >=8.0 by default local_infile is off. To load you need to first enable it.

set global local_infile = on;

load data local infile 'E:so.csv' into table dummy
fields terminated by ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Subject,@Date_entry)
set Date_entry=if(@Date_entry='',null, @Date_entry) ;

It will insert the data. If it throws error then share the result of sql_mode.

select @@sql_mode;

Note: Make sure that the csv on Date_entry column is yyyy-mm-dd hh:mm:ss format as MySQL does, btw in your csv it is not in db format. I have tested the above case in MySQL Workbench.

---

Related Topics