xjshiya xjshiya - 6 months ago 13
SQL Question

Display dates based on week() result, except Sunday

So we all know that we can get the week number of a date by using the week() function. My question is how to display the dates of that particular week, except the Sunday date?

To illustrate..

enter image description here

Based from the above example, what I want to display is..


  • 2009-05-17

  • 2009-05-18

  • 2009-05-19

  • 2009-05-20

  • 2009-05-21

  • 2009-05-22



How do I query this?

Answer
SET @GivenDate ='2016-05-27';

SET @YearNum = YEAR(@GivenDate);
SET @WeekNum=WEEK(@GivenDate);

select selected_date,weekday(selected_date) AS WeeKDate
from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) 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 YEAR(selected_date)=@YearNum
AND WEEK(selected_date)=@WeekNum
AND WEEKDAY(selected_date)<> 6; 
Comments