Bug #45903 insert date fields should give an error if inserted Data is not formated correct

• Submitted: 2 Jul 2009 10:42
• Modified: 3 Jul 2009 8:59
• Reporter: Peter Lorenz
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any (SQL)
• CPU Architecture: Any
• Tags: date, time

[2 Jul 2009 10:42] Peter Lorenz

Description:

If I insert data to an Date-Field 
the database should create an error if the inserted date is not formated in an 
supported date-format.

Now the database try to interpret the date and insert wrong data without creating an error.

How to repeat:

Create an Table with the following fields:

rec     integer autoinc primary key
datum   date
time    time

insert correct data like:
insert into testtab2 (datum, zeit) values ('2009-07-02', '15:00:35');

insert wrong formated data like:
insert into testtab2 (datum, zeit) values ('02.07.2007', '15:00:00');
insert into testtab2 (datum, zeit) values ('2007/02/07', '15:00:00');

The inserted data is something like 20.07.2002 and 07.02.2007...

Suggested fix:

simply check format

yyyy-mm-dd

strlength have to be 10, [5] and [8] have to be '-',
the rest have to be between 0 and 9.

If it isn't give back an error and don't execute this statement, 
or you can detect and support formats like dd.mm.yyyy and maybe mm/dd/yyyy 

With this check date like 0000-00-00 is still possible.

Why is this important:
Inserted data typed by user is checked by client software, sure, but
on migration from other systems (where the software inserted other date format)
this can be important to find still wrong date-formating functions.

[3 Jul 2009 8:59] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php\

This is a feature. Please see http://dev.mysql.com/doc/refman/5.1/en/datetime.html for details.