Ognjen Regoje bio photo

Ognjen Regoje
But you can call me Oggy

I make things that run on the web (mostly).
More ABOUT me and my PROJECTS.

me@ognjen.io LinkedIn

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.