[SOLVED] How to convert a string date in european format to a valid mysql date format?

How to convert a string date in european format to a valid mysql date format?

I am trying to insert 2 dates through a form in my database, date format in the form is a string like this 01/01/2020 and the field in the database is DATE so I understand it will take YYYY/MM/DD right?

I have trid this query :

INSERT INTO property_tenant (id_user, id_property, check_in, check_out) 
VALUES(1375965, 119, STR_TO_DATE("01/01/2020", '%d/%m/%y'), 
                     STR_TO_DATE("01/01/2021", '%d/%m/%y'));

I have also tried this :

DATE_FORMAT(STR_TO_DATE("01/01/2021", '%d/%m/%y'), '%Y-%m-%d')

but I keep getting this error:

code: 'ER_TRUNCATED_WRONG_VALUE', errno: 1292, sqlMessage: "Truncated incorrect date value: '01/01/2020'"

Solution

you can directly use STR_TO_DATE() function as

STR_TO_DATE('1,1,2021','%d,%m,%Y')

which already yields a date result. Indeed a date column doesn't have a format within a table. fmt part( '%d,%m,%Y' ) is just needed to express pieces of the literal conforming with the time portions(day,month,Year) to be ordered correctly.

Demo