Bug #17147 SQL MODE FEATURE
Submitted: 6 Feb 2006 6:11 Modified: 29 Mar 2011 18:38
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:NEWS OS:Any (ALL)
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[6 Feb 2006 6:11] Roberto Spadim
Description:
I was making a query like this:
SELECT data_cotacao,moeda
FROM moedas_historico 
WHERE 
moeda="USD" AND 
data_cotacao<="2006-02-31 23:59:59" AND data_cotacao>="2006-01-01 00:00:00"

the problem is that 02-31 don't exists, i changed sql_mode to allow invalid dates, but i have a problem know... if i insert invalid date it will be allowed
can we make a new sql_mode? like 
allow_invalid_select_dates
allow_invalid_update_dates
allow_invalid_delete_dates
allow_invalid_insert_dates

this can improve security in some cases for some aplications date based and not tested

How to repeat:
SELECT data_cotacao,moeda
FROM moedas_historico 
WHERE 
moeda="USD" AND 
data_cotacao<="2006-02-31 23:59:59" AND data_cotacao>="2006-01-01 00:00:00" 
whitout allow_invalid_dates
but in select it's not a problem... just in insert, update and delete

Suggested fix:
new sql_modes
[26 May 2006 19:34] Valeriy Kravchuk
Thank you for a feature request. Sorry, but I do not think that any more special date-related SQL modes are needed. If you put invalid date into column, you should be able to see the results of your INSERT.
[29 Mar 2011 18:38] Roberto Spadim
nice, closed