SELECT
floor(
datediff('2016-08-01','2016-07-01') / 7) * 2 + (
CASE WHEN
IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) >=
IF (weekday('2016-07-02') >= 5, 4,weekday('2016-07-01'))
THEN
IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) -
IF (weekday('2016-07-01') >= 5, 4,weekday('2016-07-01'))
ELSE
5 +
IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) -
IF (weekday('2016-07-01') >= 5, 4, weekday('2016-07-01'))
END
) weekdays;
when i execute this code it always return 9 i don't know where i am doing mistake please suggest me where am doing wrong. while between Saturday and Sunday between given date should be 10 please suggest me
解决方案
If you don't have a table having all the dates between your start date and end date (inclusive) then you need to adopt a query which will create all the dates between your given date range (inclusive) first. Then use WEEKDAY function of MySQL to check whether the day is Saturday or Sunday
SELECT
dateTable.Day,
DAYNAME(dateTable.Day) AS dayName
FROM
( SELECT ADDDATE('2016-07-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('2016-08-01', '2016-07-01')
) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6)
ORDER BY dateTable.Day;
Note:
EDIT:
If you need only count:
SELECT
COUNT(*) AS total
FROM
( SELECT ADDDATE('2016-07-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('2016-08-01', '2016-07-01')
) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6)