The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

How to define a date in order to import an empty date from a CSV file?

I have a CSV file containing amongst other things a couple of date columns. Some of the date values are empty - that is just "" - a comma followed by another comma.

The date rows in the table are defined like this: `PTY_USR_PASSWORD_DATE` DATE NULL DEFAULT NULL,

But when I import the data (using HeidiSQL), I get the following error: /* SQL Error (1292): Incorrect date value: '' for column 'PTY_USR_PASSWORD_DATE' at row 1 */

How should I set up the table to allow this? Or how can I modify the CSV?

John D.

Answer

That error is thrown when the sql_mode server system variable is set to strict mode. If it's not set, you can enter a blank date - a warning is generated and '0000-00-00' will be inserted.

If you can set strict mode off for the duration of the import, that should work, otherwise you'll need to replace the blank date columns in the CSV with NULL, or '0000-00-00'.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.