MySQL cannot parse yyyy-WW
Interestingly, STR_TO_DATE cannot parsea a date with yyyy-WW format.
mysql> select STR_TO_DATE('2019_45', "%x_%v");
+---------------------------------+
| STR_TO_DATE('2019_45', "%x_%v") |
+---------------------------------+
| NULL |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)
But if the day of the week is added, then it works:
mysql> select STR_TO_DATE('2019_45 Monday', "%x_%v %W");
+-------------------------------------------+
| STR_TO_DATE('2019_45 Monday', "%x_%v %W") |
+-------------------------------------------+
| 2019-11-04 |
+-------------------------------------------+
1 row in set (0.00 sec)
So, if you want to query a field that’s in that format it can be done by:
select date(str_to_date(concat(week_column, "-Monday"), "%x-%v-%W"))...
Looks kind of stupid to be honest, but not sure how else it could be done.
I do concede that it does make sense to an extent. Technically, the result is a range of dates, not exactly one date.