如何在str_to_date中使用％U for MySQL

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)

2010年10月是具有％Y％U格式的工匠（即年份跟随周数）

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

因此我尝试通过执行STR_TO_DATE（time_week，'％Y％U'）将其反转为日期时间列，但它似乎不理解周格式

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.

我当然错过了房间里的大象，但我看不清楚。

2 个解决方案

#1

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

在您的日期，失去的一天，所以在秋天的第一天，你应该添加任何一天的日期。使用concat函数添加日期

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())

在此之后你应该使用函数STR_TO_DATE（）和日期格式应为'％X％V％W'为上述日期（在CONCAT（）之后）

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

The output is 2015-01-04

输出结果为2015-01-04

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

Output is 2015-05-17

产量为2015-05-17

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

我希望这能帮到您。这篇文章类似于你的问题。

#2

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:

您不能使用格式“％X％V”将年 - 周字符串转换为日期，因为如果一周跨越月边界，则年和周的组合不能唯一地标识年和月。要将年周转换为日期，您还应指定工作日：

This is an example

这是一个例子

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

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

编辑：将'％Y％U'更改为'％X％V％W'

#1