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!

Tag which database are you using. It seems MSSql but it could be others. — Jorge Campos, Jun 06 '16 at 01:30

score 2 · Accepted Answer · answered Jun 06 '16 at 01:57

2

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;

answered Jun 06 '16 at 01:57

tuananh

747
8
17

Wow @tuananh you seem to have done all the hardwork for me... I will try this and come back. Thanks a lot. – GonHL Jun 06 '16 at 02:17
how can i edit this to generate sunday and saturday for financial year , like ( 1st april 2017 to 31st March 2018) ? – Narendra Jadon Mar 06 '17 at 07:54
Just `SET _now = '2017-04-01'; SET _endYear = '2018-03-31';` above line `while _now < _endYear DO` or you can pass it as parameters – tuananh Mar 06 '17 at 09:47

score 0 · Answer 2 · answered Jun 06 '16 at 01:36

You could create a numbers table and then select from it the days of the year which you wish to keep:

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20160101'
SET @Date2 = '20161231'

INSERT INTO feriados
SELECT DATEADD(DAY,number+1,@Date1) [Date]
FROM master..spt_values
WHERE type = 'P' AND
    DATEADD(DAY,number+1,@Date1) < @Date2 AND
    (DATEPART(WEEKDAY, DATEADD(DAY,number+1,@Date1)) = 1 OR
     DATEPART(WEEKDAY, DATEADD(DAY,number+1,@Date1)) = 7)

Alex Kudryashev · Answer 3 · 2016-06-06T03:16:02.513

This is MS SQL (t-sql) query. I think you can fix small syntax differences for MySQL.

declare @thisYear datetime=cast(year(getdate()) as varchar)+'-01-01',
@newyear datetime=cast(year(getdate())+1 as varchar)+'-01-01'
declare @frstSat datetime, @frstSun datetime

 select @frstSat= dateadd(dd,7-DATEPART(weekday,@thisYear),@thisYear) ,
@frstSun= case DATEPART(weekday,@thisYear)
when 1 then @thisYear --if 1/1 is Sunday
else dateadd(dd,8-DATEPART(weekday,@thisYear),@thisYear) end 

;with sat as (
select @frstSat dt,'Sat' WkDay
union all
select dateadd(dd,7,dt),'Sat' WkDay
from sat
where dateadd(dd,7,dt)<@newyear
)
,sun as (
select @frstSun dt,'Sun' WkDay
union all
select dateadd(dd,7,dt),'Sun' WkDay
from sun
where dateadd(dd,7,dt)<@newyear
)
--insert mytable
select * from sat
union
select * from sun
order by dt

Insert into MySQL table all Sundays and Saturdays from current year

3 Answers3