MySQL STR_TO_DATE returns NULL when using week number directive (%U, %u, %V or %v)

Question

NULL is being returned from STR_TO_DATE function when using the week number format directive (%U, %u, %V or %v) on MySQL 8.0.35. The official documentation indicates that STR_TO_DATE should be the inverse of DATE_FORMAT. I expected to get the same/similar date returned back that was provided as input to DATE_FORMAT function.

Here I've prepared a sqlfiddle, or you can use the following source code to recreate issue:

SET @_NOW=NOW(6), @_FMT='%YW%U';
SELECT
  @_NOW,@_FMT,
  DATE_FORMAT(@_NOW,@_FMT) AS `formatted`,
  STR_TO_DATE(DATE_FORMAT(@_NOW,@_FMT),@_FMT) AS `parsed`;

Is this a bug? Am I doing something wrong? Does anyone have a workaround?

Answer 1

The problem is that converting to a week doesn't convert to a specific day, but MySQL data validation must validate the year, month, and day.

The following works, because it maps to a specific day (the first day of the week):

mysql> select str_to_date('2024W04 Sunday', '%XW%V %W') as date;
+------------+
| date       |
+------------+
| 2024-01-28 |
+------------+

Answer 2

This is by design: Since a "day portion of the date" can not be derived from a year and a week the system is setting the day portion to null which sets all values in the date to null.

Put another way, you can't do this. The date value is incomplete and thus returns null. So while str_to_Date is the in general the opposite of date_format, str_To_Date can only process date formats which can be resolved to a specific Year, month, and Day (which must be a valid date) So not all formats can be "bi-directionally" converted.

Per: http://mysqltutorial.org/mysql-date-functions/mysql-str_to_date

The STR_TO_DATE() sets all incomplete date values, which are not provided by the input string, to NULL."

Per: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

If the date, time, or datetime value extracted from str cannot be parsed according to the rules followed by the server, STR_TO_DATE() returns NULL and produces a warning.

Demo wtih Warnings enabled: https://dbfiddle.uk/ac6H2HIx

SHOW WARNINGS;
SET @_NOW=NOW(6), @_FMT='%YW%U';
SELECT
  @_NOW,@_FMT,
  DATE_FORMAT(@_NOW,@_FMT) AS `formatted`,
  STR_TO_DATE('2024W04',@_FMT) AS `parsed`;
SHOW WARNINGS;

Returns:

Warning 1411    Incorrect datetime value: '2024W04' for function str_to_date

This seems to support the theory since a valid "day" can not be derrived, the whole value is set to null. Why? the system doesn't know what "Day" of the date to put in; so no day is. The system then can't verifiy it to be a valide date; and thus returns null and throws a warning.