'MySQL STR_TO_DATE with no day in the string

Question

I am trying to use STR_TO_DATE in my MySQL code, but my string does not include a day.

SELECT STR_TO_DATE('August 2020', '%M %Y')

This code returns null. I can get this function to work if I have a day in my date like:

SELECT STR_TO_DATE('August 20, 2020', '%M %d, %Y')

Is it because I'm formatting it wrong? Or does MySQL not support this functionality? If it doesn't support it is there a way around it?

Image of it shown in DBeaver

This is working fine : SELECT STR_TO_DATE('August 2020', '%M %Y') returning - 2020-08-00. — Gopi, Feb 20 '19 at 20:03
I added an image above showing that I tried that same line and it still returned [NULL] — lex449, Feb 20 '19 at 20:09
It seems the format is working with version 5.6 but with latest version 8.0.13 it's not working. — Gopi, Feb 20 '19 at 20:21

score 0 · Answer 1 · answered Feb 20 '19 at 20:26

0

The issue is related with MySql version. You can use

select STR_TO_DATE(CONCAt('1 ','August 2020'), '%d %M %Y')

answered Feb 20 '19 at 20:26

I got this format to work, but it doesn't work if there is no day. Is there anyway around this besides reverting back to a different version of MySQL? – lex449 Feb 20 '19 at 20:32
what do you mean by if there is no day? normally `0 August 2020` is not a valid date – Derviş Kayımbaşıoğlu Feb 20 '19 at 20:37
The format of the data I'm using has a string of 'August 2020', but I am unsure how to convert this to a date SQL can use. – lex449 Feb 20 '19 at 20:41
I believe that my answer is quite enough for converting it to date. – Derviş Kayımbaşıoğlu Feb 20 '19 at 20:45

1 Answers1