Research Development - 1 year ago 136
SQL Question

# how to calculate total number of saturday and sunday between two dates in mysql

``````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;
``````

WORKING DEMO

Note: `WEEKDAY` returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

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)
``````

Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download