3

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.

4
  • 2
    Do you really think that 'Sun Jun 20 00:40:27 IST 2021' matches the pattern of '%d/%m/%Y %T'?
    – mustaccio
    Mar 22, 2022 at 16:57
  • no, I want to convert to '%d/%m/%Y %T' format.
    – RDD
    Mar 22, 2022 at 16:58
  • 4
    No, you certainly don't want that; you want it to be a datetime value, which has no format. You need to tell STR_TO_DATE how to parse your string, so your format must match what is in the string you pass to it.
    – mustaccio
    Mar 22, 2022 at 17:00
  • You're confusing STR_TO_DATE with DATE_FORMAT. The first parses a date string, the second formats a date.
    – Barmar
    Mar 23, 2022 at 15:57

2 Answers 2

9

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.

2
  • IST is a named timezone. Unfortunately, STR_TO_DATE doesn't have timezone support (it's %Z in strptime()).
    – Barmar
    Mar 23, 2022 at 14:57
  • @Barmar - Thanks. That's what I was fishing for.
    – Rick James
    Mar 23, 2022 at 15:52
-1

Date-Format

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.