CODEWITHSUNDEEP
mysql
fizzy drink
fizzy drink

Reputation: 682

mysql inserts an empty date if the date isn't correct

Ok,

So I tried inserting the date '2015-02-29' (which doesn't exist, as 2015 is not a leap year) into a mysql date field. Instead of an error of sorts, it simply inserted '0000-00-00' as the date.

Why does it occur? How can I prevent or catch this? How do I have to configure the date column?

Thanks

EDIT:

The data was uploaded as a CSV file and inserted using LOAD DATA INFILE.

We can assume the following query however:

INSERT INTO TableName ('date') VALUES ('2015-02-09')

Upvotes: 0

Views: 1028

Answers (1)

lp_
lp_

Reputation: 1178

You can play with the sql_mode to change the behaviour how MySQL handles the invalid date.

According to the MySQL manual, this happens when you insert an incorrect date value:

By default, when MySQL encounters a value for a date or time type that is out of range or otherwise invalid for the type, it converts the value to the “zero” value for that type. The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range.

So a zero value is inserted and you actually get a warning about that. You can see that with the show warnings command.

insert into TableName (`date`) values ('2015-02-29');
-- 1 row(s) affected
show warnings
-- Warning  1264    Out of range value for column 'date' at row 1

You can change this behaviour to store the invalid value, if you need so (MySQL manual):

By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 5.1.7, “Server SQL Modes”.) You can get MySQL to accept certain dates, such as '2009-11-31', by enabling the ALLOW_INVALID_DATES SQL mode. This is useful when you want to store a “possibly wrong” value which the user has specified (for example, in a web form) in the database for future processing.

For example, the following should work:

set @@sql_mode='allow_invalid_dates';
insert into TableName (`date`) values ('2015-02-29');
-- 1 row(s) affected

Or, you can also change the behaviour of load data or insert to report the error:

Treatment of empty or incorrect field values differs from that just described if the SQL mode is set to a restrictive value. For example, if sql_mode='TRADITIONAL, conversion of an empty value or a value such as 'x' for a numeric column results in an error, not conversion to 0. (With LOCAL, warnings occur rather than errors, even with a restrictive sql_mode value, because the server has no way to stop transmission of the file in the middle of the operation.)

Example:

set @@sql_mode='traditional';
insert into TableName (`date`) values ('2015-02-29');
Error Code: 1292. Incorrect date value: '2015-02-29' for column 'date' at row 1

Upvotes: 2

Related Questions