The question:
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.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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