Convert Date format to insert into MySQL database

I'm receiving the date in 'Sun Jun 20 00:40:27 IST 2021' format. Which I need to insert to my MySQL database in datetime(6) format.

I used

STR_TO_DATE('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T')

and received

Incorrect datetime value: 'Sun Jun 20 00:40:27 IST 2021' for function
str_to_date error.

I also tried

date_format('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T')

and received

Incorrect datetime value: 'Sun Jun 20 00:40:27 IST 2021

Can anyone guide me to fix it?

PS: I'm using prepared statement, executeBatch() to insert data to table.

To store into a DATETIME, use STR_TO_DATE() where you describe the format of the string:

SELECT STR_TO_DATE('Sun Jun 20 00:40:27 IST 2021', "%a %b %d %H:%i:%s IST %Y");

--> 2021-06-20 00:40:27

As a separate step, you can SELECT the Datetime in a different format using DATE_FORMAT() with a different formatting string.

SELECT DATE_FORMAT('2021-06-20 00:40:27', '%d/%m/%Y');

--> 20/06/2021 00:40:27

You cannot do both conversions in a single function call.

Storing into DATETIME(6) will store 2021-06-20 00:40:27.000000. (In your example, the (6) is wasted.

If you are using LOAD DATA, there is a way to do the STR_TO_DATE as you do the insert.

I don't know how to generalize on "IST"; notice that I essentially stepped over it in my sample code.

StackExchange Database Administrators Q#310034, answer score: 9