Get dates lies between two date ranges, as per the Day of week in my sql

Question

I have fromDate, toDate and dayOfWeek in mysql. I want all the dates that lies on the particular dayofWeek and lies between fromDate and toDate. eg

fromDate- '2021-09-01 00:00:00'
toDate- '2021-09-30 00:00:00'
dayOfWeek(4,5) i.e thurday and Friday

output

output
2021-09-02
2021-09-03
2021-09-01
2021-09-10
2021-09-16
2021-09-17
2021-09-23
2021-09-24
2021-09-30

I am using MYSQLWORKBENCH 8.0

Please post sample data and desired output in text format. Also show your current attempt and describe what is the issue with it. — astentx, Sep 10 '21 at 06:36
And specify **precise** MySQL version - it is critical for your task. — Akina, Sep 10 '21 at 06:41
Do you have fromDate, toDate and dayOfWeek in one table or those are 3 different tables? — Lidbey, Sep 10 '21 at 06:53
@Lidbey they are in different table...we need to use join ..query would be something like SELECT DISTINCT slot.tl_id, dow, tl.start_datetime, tl.end_datetime FROM t_slots AS slot, listing AS tl WHERE t_date = '0000-00-00' AND tl.tl_id = slot.tl_id — Anshul Verma, Sep 10 '21 at 06:58
@astentx, i have updated the question, please check I have provided the sample data. We can ignore the time as well — Anshul Verma, Sep 10 '21 at 06:59
Slot, listing? Please include them in your request. At first it seemed you want to generate a result set of dates from the parameters, but now I suppose you rather want to select all rows from a table the dates of which match the parameters (which are in the other table)? Please explain your tables, what columns there are and what the tables represent. — Thorsten Kettner, Sep 10 '21 at 07:03
MySQL Workbench is only the tool you are using, by the way. It is not the DBMS. MySQL Workbench 8 supports all MySQL versions from 5.6 to 8.0. You see the MySQL version in the program's menu (Server / Server Status) or with SQL: `select @@version;` or `select version()`. — Thorsten Kettner, Sep 10 '21 at 07:10
@ThorstenKettner In table we have many fields but I want tl_id and dates(which is lying in fromdate- toDate criteria) — Anshul Verma, Sep 10 '21 at 07:39
*I have provided the sample data.* Sample data must be provided as CREATE TABLE + INSERT INTO textual formatted code. And it must be accompanied by desired output for shown sample data. — Akina, Sep 10 '21 at 07:46
But how are the tables related? Again: Please edit your request. Show us the tables involved. Show some sample data and expected result. We don't know your tables. You won't get any answers until we know what you are actually asking. I wouldn't insist on create table and insert statements, as long as I see what the tables contain and to what result that leads. But all you have shown us are parameters and dates. How are they connected to the tables you mentioned? — Thorsten Kettner, Sep 10 '21 at 07:47

score 1 · Answer 1 · answered Sep 10 '21 at 08:50

here for you

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2021-09-01' and '2021-09-30' 
having WEEKDAY(selected_date) in (3,4)
ORDER BY selected_date ASC;

0 is monday , 3 is thurday ... from this post

score 1 · Answer 2 · answered Sep 10 '21 at 10:36

Another way to get all Thursday and Friday between two dates. I use LAST_DAY() function (last month day) to set the end of the interval, but you can hard code a specific date:

WITH RECURSIVE days AS (SELECT '2021-09-01' as day

                        UNION ALL 
              
                        SELECT DATE_ADD(day, INTERVAL 1 DAY)
                        FROM days
                        WHERE day < LAST_DAY(day))
              
SELECT *
FROM days
WHERE weekday(day) IN (3,4);

Output:

day
2021-09-02
2021-09-03
2021-09-09
2021-09-10
2021-09-16
2021-09-17
2021-09-23
2021-09-24
2021-09-30

Get dates lies between two date ranges, as per the Day of week in my sql

2 Answers2