How to use %U in str_to_date for MySQL

Question

I have a date stored for a weekly metric in a legacy database as a string with this format:

2010 10 which was crafter with the %Y %U format (i.e years followed by week number)

So I try to reverse it into a datetime column by doing STR_TO_DATE(time_week, '%Y %U') but it does not seem to understand the week format

when I do a test on hardcoded strings, it does not work too

mysql> select str_to_date('2015 01', '%Y %U');
+---------------------------------+
| str_to_date('2015 01', '%Y %U') |
+---------------------------------+
| 2015-00-00                      |
+---------------------------------+


mysql> select str_to_date('2015 20', '%Y %U');
+---------------------------------+
| str_to_date('2015 20', '%Y %U') |
+---------------------------------+
| 2015-00-00                      |
+---------------------------------+

I'm certainly missing the elephant in the room but I cant' see what.

in your dates ('2015 01' ) there is no have any day in this format so how it is possible to come any date from str_to_date function — Vipin Jain, Feb 18 '16 at 17:11

score 1 · Answer 1 · answered Feb 18 '16 at 17:24

You cannot use format "%X%V" to convert a year-week string to a date because the combination of a year and week does not uniquely identify a year and month if the week crosses a month boundary. To convert a year-week to a date, you should also specify the weekday:

This is an example

select str_to_date('2015 20 Friday', '%X%V %W');

EDIT: Change '%Y %U' to '%X%V %W'

score 1 · Accepted Answer · edited May 23 '17 at 12:31

In your dates, missing day so first of fall you should add any day in date. use the concat function to add day

CONCAT('2015 01', ' Sunday');

after this you should use the function STR_TO_DATE() and date format should be '%X %V %W' for above date(after CONCAT())

SELECT STR_TO_DATE(CONCAT('2015 01', ' Sunday'), '%X %V %W');

The output is 2015-01-04

SELECT STR_TO_DATE(CONCAT('2015 20', ' Sunday'), '%X %V %W');

Output is 2015-05-17

I hope this will help you. this post similar to your question.

How to use %U in str_to_date for MySQL

2 Answers2