How to get date from week number and day in sql?

user_12282299 0
Viewed 979

I have both the week number and their corresponding day of week(i.e. mon,tue,wed,.....) stored in tables.
The following code is supposed to return week number from date but I'm unable to turn this around

select WEEKOFYEAR(CURDATE())

My table:

RecordID|Record|WeekID|DayofWeek
--------------------------------
1       |text1 |43    |mon
2       |text2 |43    |tue
3       |text3 |44    |wed

Desired output:

RecordID|Record|Date
--------------------------------
1       |text1 |2019/10/30
2       |text2 |2019/10/31
3       |text3 |2019/11/01

I want to retrieve the date from them(assuming current year). Is it possible in sql or can it be done only on server side?
*Dates just for representation

datetime mysql sql
3 Answers

This following sample script might help you. Hope all necessary values are available in your database and you have pass them to the function accordingly-

SELECT STR_TO_DATE('2013 10 Tuesday', '%X %V %W');

--2013 is the year value
--10 is the week number
--Tuesday is the day name

If you have all three values available in your table and run the STR_TO_DATE function providing appropriate values - this will return you a date like - "2013-03-12".

You can check the below script-

SELECT 
STR_TO_DATE(concat('2019',' ', WeekID,' ', DayofWeek), '%X %V %W') 
FROM (
    SELECT 1 RecordID, 'text1' Record, 43 WeekID,'mon' DayofWeek UNION ALL
    SELECT 2,'text2',43,'tue' UNION ALL
    SELECT 3,'text3',44,'wed'
)A;

Your final query should be as below-

SELECT 
STR_TO_DATE(concat('2019',' ', WeekID,' ', DayofWeek), '%X %V %W') 
FROM your_table_name A;

Note: Year 2019 is fixed as this value is not available in your table. If available, you can also use that column dynamically as other columns are used.

user_36523450

Your question isn't completely clear.

I guess you have the columns

  • year with values like 2001
  • WeekID with values like 36
  • DayOfWeek with values like tue.

Then, you can use an expression like this to get the DATE value. MySQL has date format strings for week and weekday.

SELECT STR_TO_DATE (CONCAT(year, '/', week, '/', weekday), '%Y/%v/%a')

Here's a fiddle. https://www.db-fiddle.com/f/iGrnkM5WgWTVPxuqxfPxdK/0

But beware, the computation of week number is a business rule subject to local and international standards. Be sure to test with dates in the first few days of several different calendar years to make sure you understand your situation.

You can read about the choices for week computation here. You use WEEKOFYEAR() to retrieve the week number; that corresponds to the %v format specifier.

user_2056080

You cant get the date from just week number and day of week, you would need the year too.

user_113806100
Related