Fixing Incorrect Date Value Errors in a MySQL CSV Import

The error says the date is incorrect and the file says 01/15/2025, a date that every person reading it understands. MySQL does not. A DATE column accepts text in the form YYYY-MM-DD and a few close relatives, and everything else is an error or, worse, a placeholder. This article reproduces the two ways this appears, the loud one and the quiet one, shows the fix that handles both the format and the awkward values, and gives the query that finds zero dates already in a table. The whole import path is in the MySQL import guide, and the column mapping used for the fix is explained in mapping CSV columns to table columns.

The most common cause

The file has dates written the way Americans write them. This is the file, with the signup_date column in MM/DD/YYYY:

id,name,email,signup_date,balance
1,Ana Silva,[email protected],01/15/2025,120.50
2,"Smith, Jane",[email protected],02/03/2025,0
3,Bo Chen,,02/20/2025,
4,Cy Dubois,[email protected],03/01/2025,15.75

A server side load, which is strict, fails on the first row:

mysql -h mysql -uroot lab <<'SQL' 2>&1
TRUNCATE customers;
LOAD DATA INFILE '/var/lib/mysql-files/customers-us-dates.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (id, name, @e, signup_date, @b) SET email = NULLIF(@e,''), balance = NULLIF(@b,'');
SELECT COUNT(*) AS rows_loaded FROM customers;
SQL

ERROR 1292 (22007) at line 2: Incorrect date value: '01/15/2025' for column 'signup_date' at row 1

ERROR 1292 (22007) at line 2: Incorrect date value: '01/15/2025' for column 'signup_date' at row 1. Nothing is loaded. A plain INSERT of a date in that format gives the same code:

mysql -h mysql -uroot lab -e "INSERT INTO customers (id, name, signup_date) VALUES (50, 'Zed', '03/14/2025')" 2>&1

ERROR 1292 (22007) at line 1: Incorrect date value: '03/14/2025' for column 'signup_date' at row 1

Both are the good outcome. The statement stops, names the column, the row and the value, and you fix the format.

The quiet failure

The behavior is documented: the LOAD DATA page says LOCAL has the same effect as IGNORE, and the SQL modes page describes what IGNORE does under strict mode.

Run the same file with LOCAL, which is how most people run it, and it does not stop. The client sends the file, the server cannot abort mid-transfer, and LOCAL is documented to behave like IGNORE: data interpretation errors become warnings and the load continues. Here is that load:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers-us-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT COUNT(*) AS rows_loaded FROM customers;
SQL

Level	Code	Message
Warning	1265	Data truncated for column 'signup_date' at row 1
Warning	1265	Data truncated for column 'signup_date' at row 2
Warning	1265	Data truncated for column 'signup_date' at row 3
Warning	1366	Incorrect decimal value: '' for column 'balance' at row 3
Warning	1265	Data truncated for column 'signup_date' at row 4
rows_loaded
4

Four rows loaded, and four Data truncated for column 'signup_date' warnings, one per row, plus the unrelated empty balance warning. If your script looked only at the exit status, which is 0, it saw success. Look at what is in the column:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers-us-dates.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (id, name, @e, signup_date, @b) SET email = NULLIF(@e,''), balance = NULLIF(@b,'');
SELECT COUNT(*) AS rows_loaded, SUM(signup_date < '1000-01-01') AS zero_dates, MIN(signup_date) AS earliest FROM customers;
SELECT id, CAST(signup_date AS CHAR) AS as_text FROM customers LIMIT 2;
SQL

rows_loaded	zero_dates	earliest
4	4	0000-00-00
id	as_text
1	0000-00-00
2	0000-00-00

Four rows, four zero dates, and the earliest date in the table is 0000-00-00. This happened on a server whose SQL mode includes NO_ZERO_DATE, the setting that is supposed to prevent zero dates. That mode stops you inserting one by hand and does not stop a local load from writing one on your behalf. The query that finds them is SUM(signup_date < '1000-01-01'), which counts any date before the year 1000, and a CAST(signup_date AS CHAR) shows the text 0000-00-00 directly. Run the first on any column in a table that was loaded this way.

The fix: convert in the statement

Read the date into a variable and parse it with STR_TO_DATE, giving it the format of the file:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers-us-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
  (id, name, @e, @d, @b)
  SET email = NULLIF(@e, ''), signup_date = STR_TO_DATE(@d, '%m/%d/%Y'), balance = NULLIF(@b, '');
SELECT id, name, signup_date, balance FROM customers ORDER BY id;
SQL

id	name	signup_date	balance
1	Ana Silva	2025-01-15	120.50
2	Smith, Jane	2025-02-03	0.00
3	Bo Chen	2025-02-20	NULL
4	Cy Dubois	2025-03-01	15.75

(id, name, @e, @d, @b) reads the date as text, and signup_date = STR_TO_DATE(@d, '%m/%d/%Y') converts it, with %m for the month, %d for the day and %Y for a four digit year. The dates came out as 2025-01-15, 2025-02-03, 2025-02-20 and 2025-03-01. The specifiers are listed in the manual's date function reference, including %b for an abbreviated month name and %y for a two digit year. If the file uses day first dates, swap %m and %d, and make sure which one it is before you load: 03/04/2025 loads without complaint in either order and means different days.

The awkward values

Real files hold more than one kind of bad date. I made a file with five rows: a good date, 0000-00-00, an empty field, 2025-02-30 and 2025-2-3. Loaded straight into the DATE column with LOCAL:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'odd-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT id, signup_date FROM customers ORDER BY id;
SQL

Level	Code	Message
Warning	1264	Out of range value for column 'signup_date' at row 2
Warning	1265	Data truncated for column 'signup_date' at row 3
Warning	1264	Out of range value for column 'signup_date' at row 4
id	signup_date
1	2025-01-15
2	0000-00-00
3	0000-00-00
4	0000-00-00
5	2025-02-03

Row 1 loaded. Rows 2, 3 and 4 became 0000-00-00, with Out of range value for the zero date and the impossible February 30, and Data truncated for the empty one. Row 5, 2025-2-3, was accepted as 2025-02-03, since MySQL tolerates a one digit month and day. These warnings are different ones for different problems, and an IGNOREd load folds them into the same stored value.

With the conversion done in SET, I can decide what each should become. This version maps empty and zero dates to NULL, and lets STR_TO_DATE reject the impossible one:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'odd-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' IGNORE 1 LINES (id, name, email, @d, balance)
  SET signup_date = CASE WHEN @d IN ('', '0000-00-00') THEN NULL ELSE STR_TO_DATE(@d, '%Y-%m-%d') END;
SHOW WARNINGS;
SELECT id, signup_date FROM customers ORDER BY id;
SQL

Level	Code	Message
Warning	1411	Incorrect datetime value: '2025-02-30' for function str_to_date
id	signup_date
1	2025-01-15
2	NULL
3	NULL
4	NULL
5	2025-02-03

The warning, Incorrect datetime value: '2025-02-30' for function str_to_date, names the bad value and the row's date is NULL. Only rows 1 and 5 hold a date. That is an honest result: the three that could not be converted are NULL, which a query can find with IS NULL, and not a date that looks like a real one. For an impossible date, a rejected row in a reject table is better than NULL, and for that you load into a VARCHAR staging table and select the failures with STR_TO_DATE(...) IS NULL AND @d <> ''.

Does changing sql_mode help

It is tempting to relax the mode. I set sql_mode to an empty string for the session and loaded the same five rows:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
SET SESSION sql_mode = '';
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'odd-dates.csv' INTO TABLE customers FIELDS TERMINATED BY ',' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT id, signup_date FROM customers ORDER BY id;
SQL

Level	Code	Message
Warning	1265	Data truncated for column 'signup_date' at row 3
Warning	1264	Out of range value for column 'signup_date' at row 4
id	signup_date
1	2025-01-15
2	0000-00-00
3	0000-00-00
4	0000-00-00
5	2025-02-03

The zero date in row 2 now loaded without a warning, and the other two warnings remained. The data is the same: 0000-00-00 in rows 2, 3 and 4. So the mode changed what MySQL tells you and not what it stores, and a quieter load is a worse one. I would go the other way and keep strict mode, use a server side load where possible, and check warnings after a local one. If your application cannot cope with NULL dates and needs a placeholder, choose one on purpose, such as 1970-01-01 or 9999-12-31, and write it in the statement.

How to tell it worked

Three checks after a load: @@warning_count is zero, the count of NULL dates equals the count of empty or invalid dates you expected, and MIN and MAX of the column are plausible. A minimum of 0000-00-00 or a maximum in the year 2099 means the conversion went wrong somewhere. Then compare one row you know by eye with the file. If you share the file with other tools, check how they read it too, and see what the PostgreSQL route does with the same dates in loading a CSV into a staging table, since the same date can mean different days to a spreadsheet and to MySQL; the mixed date formats article covers files where the format varies by row.

When the dates are wrong but valid

If every date parses and the numbers are not what the source intended, the format was guessed wrongly: a day first file read as month first gives plausible dates in the wrong months, and a value of 13/05/2025 is the only row that fails, which is a hint. Compare a small sample with the source system before trusting the whole file. Two digit years need an explicit rule, since %y follows a fixed pivot. And time zones are a separate concern: a DATE has none, but a DATETIME or TIMESTAMP loaded from a file with offsets in it either drops or converts them depending on the column type, which is a question for a different article.