MySQL: display only the dates of the days that fall on a Sat. or Sun. between two given dates? How can I get all the dates of the Saturdays and Sundays that fall between two given dates using MySQL?

Startdate = 2020-04-08
Enddate = 2020-04-21


The answer must be:

2020-04-08
2020-04-14
2020-04-15
2020-04-21


I'm a newbie to MySQL andI tried some queries that I got from the internet that show all dates between two dates now I wish to narrow those dates down to only Sat. and Sun.

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 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 t1 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 t2 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 t3 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 t4 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 '2020-04-08' and '2020-04-21
Your query does indeed generate dates - but do you really need to generate 100,000 days between 1970 and 2243-10-16 when all you need are dates from next month - see the fiddle here?  

Getting code from the internet can be great - most of us do it all the time (it's the whole raison d'être of dba.stackexchange and all of the SE sites), but we have to be careful that a general solution is really suitable for our needs.

On my own laptop I profiled your query using the technique outlined in the MySQL documentation here. 

Your query spends 0.173519 seconds executing.

If it's changed to this (commented out 3 lines):

SELECT selected_date, DAYNAME(selected_date) AS "Weekend dates"
FROM
(
  SELECT adddate(DATE(NOW(0)), t1*10 + t0) AS selected_date 
  FROM
  (select 0 t0 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 t1 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 t2 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 t3 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 t4 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
) tab
WHERE 
  (selected_date BETWEEN '2020-04-08' AND '2020-04-21')
  AND (DAYOFWEEK(selected_date) = 1 OR DAYOFWEEK(selected_date) = 7)
ORDER BY selected_date;


during which only 100 dates are generated by the inner query, only 0.000212 seconds are spent executing - almost 1000 (818) times faster. May not seem like much, but if you have (don't know your circumstances) many of these queries hitting the server, this could add up! In any case, it's obviously better to reduce your processing where reasonably possible.

The result is:

selected_date   Weekend dates
   2020-04-11        Saturday
   2020-04-12          Sunday
   2020-04-18        Saturday
   2020-04-19          Sunday


whereas you say that the data you say you want is:

2020-04-08
2020-04-14
2020-04-15
2020-04-21


The days you specify fall on Sat. and Sun. in March of 2020 and not April. It's not really important in any case - you can just change your query's BETWEEN clause to cope with any date changes.

Again, the internet is great, but you can't beat the documentation! In particular, if you are sticking with MySQL, it's worthwhile getting to know its functionality in detail and I would recommend that you bookmark these two pages - MySQL's date and time functions and MySQL's string functions.  

Finally, I would suggest that you upgrade to MySQL version 8 - it underwent a raft of very powerful enhancements that are (IMHO) essential in any dev/DBA's toolkit. Analytic - aka Window - functions, Common Table Expressions, Regular Expression enhancements inter alia  you can check them out here.

p.s. welcome  to the forum!