Why does MySQL accept fake date?
Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does MySQL accept fake date?
Hello Mark, in Versions of MySQL prior to 5.0.2 it is only checked that the year-part ranges from 1000-, the month-part from 1-12 and the day-part ranges from 1-31 within the date column. With 5.0.2 of MySQL the Dates must be legal, so 2006-02-31 is no more possible by default. You can however turn on that behavior again by using |the option ALLOW_INVALID_DATES when starting the MySQL Server. So, either you take care that valid dates are entered in your Column by checking before inserting data with your favorite scripting/programming language or upgrade to MySQL 5.0.2 which however will prevent you from inserting invalid dates in the first place. Greets Rocco | Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does MySQL accept fake date?
Hello Rocco, Thank you for your reply. Clear and simple. :) I couldn't upgrade the MySQL server on my production server just yet, but I upgraded the local test MySQL server on a Windoze machine. And it worked like a charm. :) I just needed to create a one-time table of how many days there are in each month for the next ten years (that's what the 'invalid date' test was supposed to do), and at what day of the week they start. In MySQL 5.0.2 this really worked very well. Thank you! :) Hello Mark, in Versions of MySQL prior to 5.0.2 it is only checked that the year-- part ranges from 1000-, the month-part from 1-12 and the day-part ranges from 1-31 within the date column. With 5.0.2 of MySQL the Dates must be legal, so 2006-02-31 is no more possible by default. You can however turn on that behavior again by us- ing |the option ALLOW_INVALID_DATES when starting the MySQL Server. So, either you take care that valid dates are entered in your Column by checking before inserting data with your favorite scripting/programming language or upgrade to MySQL 5.0.2 which however will prevent you from inserting invalid dates in the first place. Greets Rocco Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]