How to convert month of week along day with difrerent fields in mysql

0
votes

I have different fields with month week and day, i want to convert fields to date. Year is current year. The current table format and value's below.

Current table 

----------------------------
| ID  | Month | Week | Day |
----------------------------
| 1   |  11   |  2   |  4  |
| 2   |  09   |  3   |  5  |
| 3   |  12   |  2   |  2  |
| 4   |  12   |  4   |  5  |
----------------------------

eg 3 record : year - 2018, Month - 11, Week - Second, Day - 2(Tuesday) convert into : 2018-11-05.

Output like below

---------------------
| ID  |     Date    |
---------------------
|  1  |  2018-11-07 |
|  2  |  2018-09-06 |
|  3  |  2018-12-13 |
|  4  |  2018-12-29 |
---------------------

SELECT STR_TO_DATE(concat("2018",Month,Week(week))) from schedule

I tried the select query DAYOFWEEK and WEEK those only use for date. My process reverse.

Table

CREATE TABLE `schedule` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `month` int(2) DEFAULT NULL,
  `week` int(2) DEFAULT NULL,
  `day` int(2) DEFAULT NULL,
)
1
mysqlsqlselectdayofweekweekday
How is 2018-09-06 in the third week of the ninth month? - Gordon Linoff
And why 2018? You don't have a year column in your current table - Raymond Nijland
I will get from current year.. - Raj Mohan
Possible duplicate of How to convert number of week into date? - Madhur Bhaiya
May be not, because, they used week from year base like (33), but i create based on month base. first, second, third, four. - Raj Mohan

1 Answers

-1
votes

SQL takes the dates funny to the way we see them in the USA. YEARmonthDay all integers. Hope this helps michael