MySQL, how to insert null dates

MySQL, how to insert null dates

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

MySQL, how to insert null dates

The immediate issue in the provided scenario stems from a fundamental mismatch between SQL syntax and PHP string concatenation logic. The user's code attempts to pass NULL as an empty string ('') rather than a genuine SQL NULL value. In the snippet $date1 = NULL;, while this creates a PHP null variable, the subsequent query construction VALUES ("...". $date1 . "...") concatenates that PHP null into the final string. Depending on how the variable is handled before insertion or if it remains an empty string after the conditional check fails to convert it properly, MySQL receives an empty string literal ('') instead of a SQL NULL.

When you insert an empty string into a DATE column in MySQL, the behavior is strict: it treats the value as a string containing no characters. If the column definition allows non-NULL values but expects a date format, an empty string will either be stored as an invalid date or rejected depending on SQL modes like STRICT_TRANS_TABLES. However, if the column is defined as NOT NULL, attempting to insert anything other than a valid date (including an empty string) triggers a data truncation or out-of-range error, preventing the row from being inserted.

To correctly handle optional dates in MySQL, you must explicitly pass the SQL keyword NULL without any surrounding quotes. The source code provided tries to manipulate the PHP variable before concatenating it into the query string:

if (empty($date1)){
    $date1 = NULL; // This is a PHP null, but concatenation logic often turns this into an empty string or causes issues in raw SQL building
}
else{
    // ... date formatting logic
}

The core flaw here is the use of raw string interpolation ("'.$string1.'") combined with manual variable manipulation. When $date1 is NULL, concatenating it often results in an empty string being inserted into the query buffer. The fix requires ensuring that when a date is missing, the generated SQL query explicitly contains the word NULL.

A safer and more maintainable approach involves using prepared statements or ensuring the variable holds the exact text required by SQL. If you are constructing raw queries as shown in the source material, you must force the value to be the string 'NULL' (with quotes) only if you want an empty string, but without quotes for a true null. However, since the user's code sets $date1 = NULL, and then concatenates it, PHP treats NULL as an empty string during concatenation unless explicitly cast or checked.

Here is the corrected logic pattern to ensure MySQL receives a valid NULL:

if ($date1) {
    // Parse and format valid date strings here
    $date1 = '"' . $date1part[2] . '/' . $date1part[1] . '/' . $date1part[0] . '"';
} else {
    // Explicitly set the variable to the SQL keyword without quotes
    // Or better, handle the logic inside the query construction
}

A more robust pattern that avoids string concatenation pitfalls entirely is to conditionally build the value for the query:

$date1Value = empty($date1) ? 'NULL' : '"'.str_replace('/', '-', $date1).'"'; 
// Note: Ensure date format matches column type (YYYY-MM-DD usually preferred over MM/DD/YYYY unless using specific collation)

If the application framework supports it, utilizing PDO or mysqli prepared statements is highly recommended. With prepared statements, you can pass a PHP null directly to the driver, which automatically translates it to SQL NULL without needing to manipulate strings:

$stmt = $pdo->prepare('INSERT INTO table (column_s1, column_s2, column_d1, column_d2) 
                       VALUES (?, ?, ?, ?)');
// Pass null directly for date fields when data is missing
$stmt->execute([$string1, $string2, isset($date1) ? $formattedDate : null, isset($date2) ? $formattedDate : null]);

This approach eliminates the risk of SQL injection and ensures that database drivers handle type conversion correctly. It also sidesteps the ambiguity of whether a PHP NULL becomes an empty string or a SQL NULL during string concatenation.

When dealing with date columns, it is also worth noting that MySQL often expects dates in the YYYY-MM-DD format. The source code converts to MM/DD/YYYY and wraps it in quotes. While this works if the column accepts string dates, standardizing on DATE types usually implies YYYY-MM-DD. If you are inserting a real date value, ensure the formatting matches the column's expected input to avoid implicit casting errors or storage issues.

In summary, the inability to insert null values in the original code arises from PHP's handling of null during string concatenation, which frequently results in empty strings rather than SQL NULLs. By explicitly constructing the query to include the literal NULL keyword when data is absent, or by leveraging prepared statements that handle type mapping automatically, you can ensure consistent behavior regardless of whether the date field contains a value or remains empty.

Our network