Error while inserting date - Incorrect date value:

Question

I am having a column called 'today' and the type is 'DATE' in MySQL.

When I am trying to add the date in the format '07-25-2012' I am getting the below error:

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

Answer 1

As MySQL accepts the date in the 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 the following way:

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

Similarly, if you want to select the date in a different format other than MySQL format, you should try DATE_FORMAT function

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

Want to be a SQL expert? Intellipaat's SQL Certification will be your best choice. Go ahead with the training!