Error while inserting date - Incorrect date value:

I have a column called today and the type is DATE.

When I try to add the date in the format '07-25-2012' I get the following error:

Unable to run query:Incorrect date value: '07-25-2012' for column

Answers


As MySql accepts the date in y-m-d format in date type column, you need to STR_TO_DATE function to convert the date into yyyy-mm-dd format for insertion in following way:

INSERT INTO table_name(today) 
VALUES(STR_TO_DATE('07-25-2012','%m-%d-%y'));  

Similary, if you want to select the date in different format other than Mysql format, you should try DATE_FORMAT function

SELECT DATE_FORMAT(today, '%m-%d-%y') from table_name; 

Generally mysql uses this date format 'Y-m-d H:i:s'


You need to convert the date to YYYY-MM-DD in order to insert it as a MySQL date using the default configuration.

One way to do that is STR_TO_DATE():

insert into your_table (...)
values (...,str_to_date('07-25-2012','%m-%d-%Y'),...);

This is the date format:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

Why do you insert '07-25-2012' format when MySQL format is '2012-07-25'?. Actually you get this error if the sql_mode is traditional/strict mode else it just enters 0000-00-00 and gives a warning: 1265 - Data truncated for column 'col1' at row 1.


you need to use YYYY-MM-DD format to insert date in mysql


You can use "DATE" as a data type while you are creating the table. In this way, you can avoid the above error. Eg:

CREATE TABLE Employee (birth_date DATE);
INSERT INTO Employee VALUES('1967-11-17');

Need Your Help

use a css stylesheet on a jinja2 template

python html css flask jinja2

I am making a website using html, css, flask and jinja2.

Rolling garbage collector logs in java

java logging garbage-collection

Is it possible to do a rolling of garbage collector logs in Sun JVM?