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
STR_TO_DATE('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T')
Incorrect datetime value: ‘Sun Jun 20 00:40:27 IST 2021’ for function
I also tried
date_format('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T')
Incorrect datetime value: ‘Sun Jun 20 00:40:27 IST 2021
Can anyone guide me to fix it?
PS: I’m using
executeBatch() to insert data to table.
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.
To store into a
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");
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');
You cannot do both conversions in a single function call.
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