CODEWITHSUNDEEP
mysql
Simhachalam Sopeti
Simhachalam Sopeti

Reputation: 81

In mysql How to find number of sundays in a month

In mysql how to find number of sundays in month by usnig month in where clause

Ex: 

month(log_date)=month(now());
    month(log_date) = 12;

output: 5 sundays

Upvotes: 1

Views: 3652

Answers (4)

Yogistra Anderson
Yogistra Anderson

Reputation: 1

Try this function, you use like

count_days_in_month($date_month,$day_idx);

$day_idx is 1 - 7 where Sunday is 1, Saturday is 7 eg.

select count_days_in_month('2020-02-01',1);

delimiter //
create or replace function count_days_in_month(input_date DATE,dayindex INT)
returns INT
DETERMINISTIC
    BEGIN
        declare start_day_idx INT default (select dayofweek( date_sub(input_date,INTERVAL (DAYOFMONTH(input_date)-1) DAY )) from dual);
        declare days_in_month INT default (SELECT dayofmonth(LAST_DAY(input_date)));
        declare days_over_28  INT default (days_in_month-28);
        
        if days_in_month > 28 then 
            if (days_over_28 > (dayindex-start_day_idx)) and ((dayindex-start_day_idx) >= 0) then               
                return 5;
            else
                return 4;
            end if;
        else 
            return 4;
        end if;
    END//

Eg to test the count of all days in a month:

select 
    count_days_in_month('2020-02-01',1) 'Sun',
    count_days_in_month('2020-02-01',2) 'Mon',
    count_days_in_month('2020-02-01',3) 'Tue',
    count_days_in_month('2020-02-01',4) 'Wed',
    count_days_in_month('2020-02-01',5) 'Thur',
    count_days_in_month('2020-02-01',6) 'Fri',
    count_days_in_month('2020-02-01',7) 'Sat'   
from 
    dual;//

Upvotes: 0

Suresh Chaudhary
Suresh Chaudhary

Reputation: 698

In mysql just copy and paste the query given below. It will give you number of Sundays in current month.

WITH RECURSIVE offdays as(
SELECT 
LAST_DAY(CURDATE()-INTERVAL 1 MONTH) + INTERVAL 1 DAY AS `Date`,
DAYNAME(LAST_DAY(CURDATE()-INTERVAL 1 MONTH) + INTERVAL 1 DAY) AS `DayName`
UNION ALL
SELECT `Date` + INTERVAL 1 DAY, DAYNAME(`Date` + INTERVAL 1 DAY) 
FROM offdays WHERE `DATE` < LAST_DAY(CURDATE()) 
) SELECT count(*) FROM offdays where DAYNAME(DATE) = 'Sunday';

you will get number of sundays in current month. Just replace the CURDATE() with any date. The query will give you the number of Sundays of the month of the date supplied.

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Possible way to do it which avoids using any tables. This finds the number of days in the month, and depending on that and what day of the week the last day is then it just returns a value

SELECT CASE DAYOFMONTH(LAST_DAY(NOW()))
        WHEN 31 THEN
            CASE DAYOFWEEK(LAST_DAY(NOW()))
                WHEN 1 THEN 5
                WHEN 2 THEN 5
                WHEN 3 THEN 5
                ELSE 4
            END 
        WHEN 30 THEN
            CASE DAYOFWEEK(LAST_DAY(NOW()))
                WHEN 1 THEN 5
                WHEN 2 THEN 5
                ELSE 4
            END 
        WHEN 29 THEN
            CASE DAYOFWEEK(LAST_DAY(NOW()))
                WHEN 1 THEN 5
                ELSE 4
            END 
        ELSE 4
    END

Upvotes: 2

Aman Kumar
Aman Kumar

Reputation: 4547

SQL query for get total sunday in given month from DB

USE BETWEEN :- BETWEEN operator selects values within a given range.

Note :- DAYOFWEEK actually returns 1 for Sunday

SELECT count(*) AS total_sunday FROM `table` WHERE DAYOFWEEK(`date`) = 1 BETWEEN '2017-11-01' AND '2017-11-30';

Upvotes: 1

Related Questions