Convert Date format to insert into MySQL database

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply