mysql - 在一个mysqli查询中给出的年，周号和工作日时返回DATE(Return DATE when year, week number and weekday given in one mysqli query)

Question

I have the following table :

(我有下表：)

--------------------------------------
| Year |  Week  |  Weekday |   Date  |
--------------------------------------
| 2019 |   20   |     3    |         |
| 2019 |   10   |     4    |         |
| 2019 |    2   |     1    |         |
| 2019 |   41   |     2    |         |
--------------------------------------

I would like the last column to be filled with the exact date ("YMd"), based on year, week and weekday from the other columns.

(我希望在最后一列中根据其他列中的年，周和周日填充确切的日期（“ YMd”）。)

Is it possible to do this in one MYSQLi query ?

(是否可以在一个MYSQLi查询中执行此操作？)

So far, I tried:

(到目前为止，我尝试了：)

UPDATE table 
SET Date = STR_TO_DATE(Year Week Weekday, '%X %V %w')

AND

(和)

UPDATE table 
SET Date = DATE(STR_TO_DATE(Year Week Weekday, '%X %V %w'))

AND

(和)

UPDATE table 
SET Date = DATE(DATE_FORMAT(Year Week Weekday, '%Y %m %d'))

Without succes.

(没有成功。)

Any ideas on how to get this in one query ?

(关于如何在一个查询中获得此的任何想法？)

  ask by Peter Verreyde translate from so

Answer 1

With MAKEDATE() you can calculate the date like this:

(使用MAKEDATE()您可以像这样计算日期：)

UPDATE tablename 
SET Date = MAKEDATE(Year, (Week - 1) * 7 + Weekday);

See the demo .

(参见演示 。)
Results:

(结果：)

| Year | Week | Weekday | Date       |
| ---- | ---- | ------- | ---------- |
| 2019 | 20   | 3       | 2019-05-16 |
| 2019 | 10   | 4       | 2019-03-08 |
| 2019 | 2    | 1       | 2019-01-08 |
| 2019 | 41   | 2       | 2019-10-09 |