Gui O Gui O - 5 months ago 22
MySQL Question

Mysql date warning data truncated

I'm having an interesting issue with Mysql DATE format.
I have this table :

| id | int(11) | NO | PRI | NULL | auto_increment |
| file_path | varchar(255) | YES | | NULL | |
| date_export | date | YES | | NULL | |


When i'm updating a row using the date function : NOW(), the date is updated with this format :

'2014-01-23'


But when i'm using another date format, like hand-written one like :

update backup_conf_allied set date_export='2014-23-01' where file_path='IDF-952584-SW1' ;


The date_export column transforms into :

'0000-00-00'


Warning table tells me that :

| Warning | 1265 | Data truncated for column 'date_export' at row 3628 |


Why? The date format is the same as NOW() function.
Thanks.

Answer

Posted query

update backup_conf_allied set `date_export='2014-23-01'` where file_path='IDF-952584-SW1' ;

What it should be

update backup_conf_allied set `date_export='2014-01-23'` where file_path='IDF-952584-SW1' ;

MySQL Support DATE format as 'YYYY-MM-DD' , Year then Month then Date, So you are updating a Date column with wrong value "2014-23-01" , There are only 12 months in year, you are using month 23 which is invalid that's MySQL is converting it to ZERO DATE (0000-00-00)