-1

I am using the code below to get a list of dates between a date range.

SELECT ADDDATE('2012-02-10', INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2012-02-15', '2012-02-10')

Now I wanted to only list dates that matches specific days of the week (.i.e. it will only return if the date is a Monday or Thursday).

Any ideas?

ubay25
  • 23
  • 7

1 Answers1

2

Use the function WEEKDAY() which starts with Monday to figure it out.

e.g :

where WEEKDAY(DAY) = 'Monday';

for more than one day use the in keyword:

where WEEKDAY(DAY) in ('Monday', 'Tuesday','Friday');

as you are using an alias you have to use a having instead of where:

having WEEKDAY(DAY) in ('Monday', 'Wednesday');

Final query:

SELECT ADDDATE('2018-03-01', INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2018-03-20', '2018-03-01') having WEEKDAY(DAY) in        
('Monday', 'Wednesday');
Fabian Steiner
  • 121
  • 7
  • So I added that to the end of the query (i.e. AND WEEKDAY(DAY) = 'Monday';) but it fails. – ubay25 Feb 26 '18 at 12:03
  • "Unknown column 'DAY' in where clause" – ubay25 Feb 26 '18 at 12:53
  • That is caused by the where clause not being able to see aliases...maybe you could try adapting the ADDDATE function? – Fabian Steiner Feb 27 '18 at 13:44
  • I am not sure what you mean, can you please give me an example? – ubay25 Feb 27 '18 at 14:20
  • First explain what you are trying to do, than I can figure out a solution. If you do not describe the aim of your code, I can not make my answer more precise. Because if you are really just trying to do what you described above, it's the most complex way to do it. – Fabian Steiner Feb 28 '18 at 14:30
  • Let me make my question clear: why do you need this list of dates? – Fabian Steiner Feb 28 '18 at 14:46
  • Hi, I basically have a combination of a date range and days of the week (0-6). Now, for example I have a date range 2018-03-01 to 2018-03-20 and days of the week 1 and 3 (which is Monday and Wednesday). I want to list the dates within the date range that matches the days of the week 1 and 3. – ubay25 Feb 28 '18 at 18:04
  • Ok i still wonder why you would want to do that but I'll edit my answer. – Fabian Steiner Mar 01 '18 at 15:27
  • Spot on! Thank you for this @Fabian. My main goal is to match these dates to a table with dates for identifying conflicts. – ubay25 Mar 02 '18 at 15:32