I was working on a Web portal, where there was a use case to import the csv or xls to a MySql database. In the database there was a field for date with the date data type. And from the xls or csv when we are importing, it imports as the string. So when you are trying to insert a string or text in date data type, it adds as 0000-00-00 which is wrong.
For that, to insert it correctly we need to convert the date string to date format before inserting.
Consider the below csv file to be imported. Dob (Date of Birth) is the column that contains the date.
First row is the header. Now the content of the DOB, DOJ and Anniversary columns has to be converted to date format. Below is the code to be considered to convert it.
Quick solution (original approach)
//For DOB
$time = strtotime($data[7]);
$data[7] = date('Y-m-d', $time);
// For DOJ
$time = strtotime($data[8]);
$data[8] = date('Y-m-d', $time);
//For Anniversary
$time = strtotime($data[11]);
$data[11] = date('Y-m-d', $time);
strtotime(string) is the function that converts the string to time format. Now this has to be converted to required date format. That can be done using date(format, time) function.
Format you can use the required format. Here I am using yyyy-mm-dd format for that we can use Y-m-d. Now the $data[7], $data[8] and $data[11] contains the date in date datatype and also in yyyy-mm-dd format.
That's it. Happy Coding
Explanation and important checks
strtotime()returns FALSE on failure. If you pass that FALSE todate('Y-m-d', $time), PHP converts it to1970-01-01or you may end up inserting an incorrect value — your original symptom of0000-00-00usually happens when MySQL receives an invalid date string.- Always check the return value before formatting.
Example with checks:
// Example check before formatting
$time = strtotime($data[7]);
if ($time === false || empty($data[7])) {
// handle invalid or empty input: set to NULL or skip
$data[7] = null; // or use a default
} else {
$data[7] = date('Y-m-d', $time);
}
Safer approach: DateTime and createFromFormat()
strtotime() works for many common formats, but if your CSV uses a specific format (for example dd/mm/yyyy or mm-dd-yyyy), prefer DateTime::createFromFormat() so you can parse deterministically.
Example parsing a dd/mm/yyyy input and handling failure:
$dateString = trim($data[7]); // e.g. "31/12/1980"
if ($dateString === '') {
$data[7] = null; // treat empty as NULL in DB
} else {
$dt = DateTime::createFromFormat('d/m/Y', $dateString);
$errors = DateTime::getLastErrors();
if ($dt === false || $errors['warning_count'] > 0 || $errors['error_count'] > 0) {
// parsing failed — fallback or log
// try a fallback with strtotime:
$time = strtotime($dateString);
if ($time !== false) {
$data[7] = date('Y-m-d', $time);
} else {
$data[7] = null; // or flag this row for manual review
}
} else {
// convert to MySQL DATE format
$data[7] = $dt->format('Y-m-d');
}
}
This approach avoids ambiguous parsing and is more robust when you know the input format.
Inserting into MySQL safely (use prepared statements)
Don't concatenate date strings into SQL. Use parameterized queries. Example using PDO inserting a DATE (or NULL):
// assume $pdo is a configured PDO instance
$sql = "INSERT INTO people (name, dob, doj, anniversary) VALUES (:name, :dob, :doj, :anniv)";
$stmt = $pdo->prepare($sql);
// $data[7], $data[8], $data[11] now contain either 'Y-m-d' strings or null
$stmt->execute([
':name' => $data[0],
':dob' => $data[7], // PDO will convert null to SQL NULL
':doj' => $data[8],
':anniv' => $data[11]
]);
If you must use mysqli, use prepared statements similarly.
Handling multiple input formats
- If the CSV may contain multiple date formats, implement a normalization function:
- Try DateTime::createFromFormat for a list of expected formats.
- Fallback to
strtotime(). - If still failing, log the row and keep it for manual review.
Example helper:
function normalizeDateToYmd($input, array $formats = ['d/m/Y', 'm/d/Y', 'Y-m-d']) {
$s = trim($input);
if ($s === '') return null;
foreach ($formats as $fmt) {
$dt = DateTime::createFromFormat($fmt, $s);
$errors = DateTime::getLastErrors();
if ($dt !== false && $errors['warning_count'] === 0 && $errors['error_count'] === 0) {
return $dt->format('Y-m-d');
}
}
// fallback to strtotime
$t = strtotime($s);
if ($t !== false) return date('Y-m-d', $t);
return null; // unable to parse
}
Edge cases and tips
- Empty or invalid date fields: insert SQL NULL instead of '0000-00-00'.
- Timezones: DATE in MySQL has no timezone, but if your CSV has times or timezone offsets, normalize before truncating.
- Excel dates: XLS/XLSX may export dates as Excel serial numbers. Use a library (e.g., PhpSpreadsheet) and convert Excel date serials to PHP dates.
- Logging: collect rows with unparseable dates and report them rather than silently inserting NULL.
- Input sanitation: always trim input and remove non-printable characters before parsing.
References
- strtotime: http://php.net/manual/en/function.strtotime.php
- date: http://php.net/manual/en/function.date.php
- DateTime::createFromFormat: https://www.php.net/manual/en/datetime.createfromformat.php
That's it. Happy Coding