CODEWITHSUNDEEP
mysql
Rahul
Rahul

Reputation: 5636

How to skip the column if dayname equals to Saturday or Sunday in Mysql query

I just want to show the dates, suppose if i select a date of 1st dec 2013 then we need to show next 7 days like

2013-12-01 2013-12-02 2013-12-03 2013-12-04 2013-12-05 2013-12-06 2013-12-07

Here is mysql query

select
cast('2013-12-01' as date) AS `1`,
(cast('2013-12-01' as date) + interval 1 day) AS `2`,
(cast('2013-12-01' as date) + interval 2 day) AS `3`,
(cast('2013-12-01' as date) + interval 3 day) AS `4`,
(cast('2013-12-01' as date) + interval 4 day) AS `5`,
(cast('2013-12-01' as date) + interval 5 day) AS `6`,
(cast('2013-12-01' as date) + interval 6 day) AS `7`,
(cast('2013-12-01' as date) + interval 7 day) AS `8`,
(cast('2013-12-01' as date) + interval 8 day) AS `9`,
(cast('2013-12-01' as date) + interval 9 day) AS `10`,
(cast('2013-12-01' as date) + interval 10 day) AS `11`

It gives me all days upto 2013-02-11 but I want to skip those columns whose dayname = "Saturday" or "Sunday", how i can solve this. I tried alot but failed.Hope you help me.

Thanks in advance.

Upvotes: 1

Views: 298

Answers (1)

valex
valex

Reputation: 24134

Something like this:

select GROUP_CONCAT(dt)
FROM
(
select
   (cast('2013-12-01' as date) + interval t.n day) dt
FROM
(
select 0 as n union all
select 1 as n union all
select 2 as n union all
select 3 as n union all
select 4 as n union all
select 5 as n union all
select 6 as n union all
select 7 as n union all
select 8 as n union all
select 9 as n union all
select 10 as n union all
select 11 as n union all
select 12 as n union all
select 13 as n) t
WHERE WEEKDAY((cast('2013-12-01' as date) + interval t.n day)) not in (5,6)
ORDER BY dt
LIMIT 7
) t1

SQLFiddle demo

Upvotes: 4

Related Questions