Function Friday13 sql

Question

I am reviewing the functions but I cannot solve this one;

Write a function that returns every Friday 13th during a specific year.

Example:

SELECT * FROM martes13(2020);
13/01/2020
13/03/2020
13/08/2020

My unfortunate attempt, do not pay much attention.

DECLARE

diaInicial date;
diaFinal date;
anio1 date;
anio2 date;
auxData date;
dates date[];

BEGIN

diaInicial := ('01/' || '01/' || anio ) :: date;
diaFinal := diaInicial + '1 YEAR' :: interval;
anio2:= date_part('year',diaFinal);

FOR i IN 1..12 BY 1 LOOP
     FOR j IN 1..30 BY 1 LOOP
       diaInicial := anio || '-' || i || '-' || j;
       if(date_part('dom',auxData)==13 and date_part('dow',auxData)==5)then
          dates[j] := diaInicial;
       end if;
     end loop;
end loop;
return dates;
END;

There's no way to solve it no matter how hard I try, I understand that I have to use dates, years intervals and counters but it does not work out. Any help or information could be of use to me.

Thanks in advance.

Have a recursive cte that generates a calendar. SELECT from the cte, use WHERE to get 13 and Friday. — jarlh
– jarlh, Commented Jun 10, 2021 at 9:59

Laurenz Albe · Accepted Answer · 2021-06-10 10:45:27Z

2

That can be solved with a simple SQL statement:

SELECT CAST(d AS date)
FROM generate_series(
        TIMESTAMP '2020-01-13',
        TIMESTAMP '2020-12-13',
        INTERVAL '1 month'
     ) AS thirteen(d)
WHERE EXTRACT (dow FROM d) = 5;

You could wrap that in an SQL function.

answered Jun 10, 2021 at 10:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Function Friday13 sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related