List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:April 5 2008 9:21am
Subject:Re: MySQL allow user to insert invalid date (month = 0) in
non-strict mode.
View as plain text  
Hi!

>>>>> "Sergei" == Sergei Golubchik <serg@stripped> writes:

Sergei> Hi!
Sergei> On Apr 03, Krunal Bauskar wrote:
>> Hi,
>> 
>> I recently discovered that values of date datatypes such as:
>> 1000-00-00
>> 1900-11-00
>> 9999-00-31
>> basically xxxx-00-00 are all valid values in non-strict mode (set
>> sql_mode = ' ';)
>> No warnings shown for these inserts.
>> But when the mode is traditional, I get a proper error.
>> 
>> Is this the correct behavior?
>> I fail to see any situation where 0 can be considered a valid month or day.

Sergei> Here's a situation: you have a library, and maintain the list of books
Sergei> in the database. You have a DATE column with 'Publication date'. But
Sergei> sometimes you don't know the exact date, but only the year - in this
Sergei> case month and day are 0.
 
Strict mode 'more or less' tells you that instead of warning you
should get an error.  Normally in MySQL you don't get a warning for 00
in days or months and days.

If you don't want to have ZERO in a date field, add modes
NO_ZERO_IN_DATE and possible NO_ZERO_DATE to your sql mode and then
you are fine.

Regards,
Monty
Thread
MySQL allow user to insert invalid date (month = 0) in non-strict mode.Krunal Bauskar3 Apr
  • Re: MySQL allow user to insert invalid date (month = 0) innon-strict mode.Sergei Golubchik3 Apr
    • Re: MySQL allow user to insert invalid date (month = 0) in non-strict mode.Krunal Bauskar3 Apr
      • Re: MySQL allow user to insert invalid date (month = 0) innon-strict mode.Sergei Golubchik3 Apr
      • Re: MySQL allow user to insert invalid date (month = 0) in non-strict mode.Chad MILLER3 Apr
    • Re: MySQL allow user to insert invalid date (month = 0) innon-strict mode.Michael Widenius5 Apr