Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 995
Floyd Lawton Avatar asked Oct 27 '19 17:10

Floyd Lawton


People also ask

How do I get the day of the week from a date in SQL?

SQL Server has a couple of inbuilt functions to get the day of week from the given date. To get the name of the day of week, you can use DATENAME function and to get the number of the day of week, you can use DATEPART function.

How do I get current date and day in SQL?

SQL Server GETDATE() Function The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format.

How do I get the first day of a week number in SQL?

Option 1: Sunday as the First Day of the WeekDATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday; The function DATEADD() takes three arguments: a datepart, a number, and a date.


1 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.

like image 188
mkRabbani Avatar answered Oct 21 '22 10:10

mkRabbani