How to Handle NULL Values in MySQL Datetime Fields

MySQL accepts NULL values in datetime fields, despite the common misconception. To assign a NULL value to a datetime field, simply leave the variable empty or send the value NULL explicitly.

Insert NULL Values Using MySQL

CREATE TABLE datetimetest (testcolumn DATETIME NULL DEFAULT NULL);
INSERT INTO datetimetest (testcolumn) VALUES (NULL);

Insert NULL Values Using PHP Prepared Statements

When using prepared statements, bind variables as usual. PHP variables containing NULL values will be stored as NULL in MySQL.

$stmt = $conn->prepare("UPDATE users SET bill_date = ? WHERE user_id = ?");

if (isset($bill_date)) {
    $stmt->bind_param("si", $bill_date, $user_id);
} else {
    $stmt->bind_param("si", NULL, $user_id);
}

Note on PHP Empty Variables

Ensure that PHP variables contain NULL, not an empty string. An empty string will result in an error:

Incorrect datetime value: '' for column 'bill_date' at row 1

Therefore, send NULL explicitly or leave the variable empty to avoid this issue.