Storing dates with zeroes (Invalid datetime format)-CodePudding

I read in the MySQL docs that I can store birthdates with 00 if I don't know the month or day, which is exactly what I need, but is this only valid for an old MySQL version or with some option turned on?

MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'.

Source: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

I get this when I run my seeder.

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '1806-00-00' for column 'birth_date' at row 1

Any other suggestions to solve this?

CodePudding user response：

Issue this SQL statement right after you connect to MySQL, eash time you connect.

SET @@sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_IN_DATE', '');

That will turn off the NO_ZERO_IN_DATE mode for your connection, allowing you do to what you ask.

CodePudding user response：

Think of integers. How would you store a value like "some integer between 20 and 30"?

You could store the number 20, and write application code that assumes the value could be in the range been the value N and N 10.
You could store the number 2, with the understanding that this is really the value N divided by 10.
You could store a string value instead of using the INTEGER data type. You'd store a string that is literally '20-30'.
You could store two values, one 20 and the other 30, i.e. the lower and upper end of the range.
Something else?

The point is that there is no way to represent a range of integers in a single scalar integer value.

If you need to store inexact dates like Year-Month with no specific day, you may not be able to use a single column of DATE, DATETIME, or TIMESTAMP data types. Those data types are for specific values, not ranges.

You could store an integer which is YYYYMM, or you could store the date which is the first date of the month, or two dates which are the lower and upper ends of the range, or you could store year, month, an day as individual columns, or some other solution. Any of these solutions require you to write application code to handle them in special ways. You can't just use the DATE data type in its default way.

MySQL historically allowed zeroes in the date components, but this results in nonsense values. How do you do date arithmetic with a date value like 2021-10-00? Is that one day before 2021-10-01? Is it also one day after 2021-09-30? How is that possible for a date to be between those two real dates? You get the idea.