0

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 ("Y-M-d"), based on year, week and weekday from the other columns. Is it possible to do this in one MYSQLi query ? 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 ?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Peter Verreyde
  • 39
  • 7
  • Thx for the comment, forpas. What I want to do is get the date in one column, based on the info on the other columns. So my main problem is getting the correct syntaxis, ie the combination of DATE_FORMAT or MAKEDATE and UPDATE. – Peter Verreyde Dec 01 '19 at 13:47

1 Answers1

1

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

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 |
forpas
  • 160,666
  • 10
  • 38
  • 76