Insert into MySQL table all Sundays and Saturdays from current year

Question

I have this tiny table with just "fecha" field in it.

I need a MySQL query that inserts every sunday and saturday of the year to the table.

My own research got me to the point where i know i need to do something like this:

DECLARE diaRecorrido date();
SET diaRecorrido = date(now());

WHILE DATEPART(WEEKDAY,diaRecorrido) = 1 or DATEPART(WEEKDAY,diaRecorrido) = 7
    BEGIN
        INSERT INTO feriados (fecha)
        VALUES (diaRecorrido)

    IF diaRecorrido=2017/01/01
    THEN
    LEAVE lazo;
    END IF;

    END;

Any guidance is much apreciated!

Answer 1

I think, you should use DAYOFWEEK()

create PROCEDURE generateSundayAndSaturday()
BEGIN
DECLARE _now DATETIME;
DECLARE _endYear DATETIME;    
SET _endYear = DATE_FORMAT(NOW() + INTERVAL 1 YEAR ,'%Y-01-01');
SELECT now() into _now from dual;    
while _now < _endYear DO
  if DAYOFWEEK(_now) = 7 THEN -- Saturday
    -- insert into
    SET _now = _now + INTERVAL 1 DAY;
  ELSEIF DAYOFWEEK(_now) = 1 THEN -- Sunday
    -- insert into
    SET _now = _now + INTERVAL 6 DAY;
  ELSE
    SET _now = _now + INTERVAL 1 DAY;
  END IF;
END WHILE;    

END;