According to the SQL standard, valid date values are described as follows:
Table 9 — Valid values for datetime fields
- YEAR 0001 to 9999
- MONTH 01 to 12
- DAY Within the range 1 to 31, but further constrained by the value of MONTHand YEAR fields, according to the rules for well-formed dates in the Gregorian calendar.
MariaDB allows dates with zero YYYY, MM, DD components with an empty sql_mode, and has two flags to provide a more SQL standard behavior:
- NO_ZERO_DATE disallows dates '0000-00-00' and datetimes '0000-00-00 00:00:00' (when all components are zeros)
- NO_ZERO_IN_DATE disallows dates 'YYYY-00-00' (i.e. non-zero YYYY, but zero MM-DD)
- NO_ZERO_IN_DATE disallows dates 'YYYY-MM-00' (i.e. non-zero YYYY and non-zero MM, but zero DD)
- NO_ZERO_IN_DATE disallows dates 'YYYY-00-DD' (i.e. non-zero YYYY and non-zero DD, but zero MM)
This combination provides best SQL standard compatibility:
However, dates with zero year are still possible:
In the above example, '0000-01-01' is not standard, because it has zero YYYY.
But there is no a way to disallow such values, as neither NO_ZERO_DATE nor NO_ZERO_IN_DATE disallow it.
Under terms of this task we'll change the meaning of NO_ZERO_IN_DATE to disallow all DATE and DATETIME values that have zero in any component of YYYY-MM-DD.
The above two scripts will return errors.