Ognjen Regoje bio photo

MY NAME IS
Ognjen Regoje
BUT YOU CAN CALL ME OGGY


I make things that run on the web (mostly).
More /ABOUT me.

me@ognjen.io Twitter LinkedIn Github

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.

#technical