15,883,709 members
See more:
I am reviewing the functions but I cannot solve this one;

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

Example:

```SELECT * FROM martes13(2020);

13/02/2020
13/03/2020
13/12/2020```

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.

What I have tried:

```DECLARE

auxData date;
dates date[];
BEGIN

anio2:= date_part('year',data_fi);
auxData:=anio||'-'||mes||'-'||1;

FOR i IN 1..12 BY 1 LOOP

FOR j IN 1..30 BY 1 LOOP

auxData:=anio||'-'||i||'-'||j;
if(date_part('day',auxData)==13 and date_part('dow',auxData)==2)then

dates[j]=auxData;

end if;
end loop;
end loop;
return dates;```
Posted
Updated 10-Jun-21 1:05am
Richard MacCutchan 10-Jun-21 5:17am
The query should be: IF dayofmonth == 13 AND dayofweek == TUESDAY ...

Solution 1

Simple: select the 13th of every month in the year, and then exclude those which don't fall on a Thursday.

For SQL Server:
SQL
```WITH cteThirteens (Thirteenth) As
(
SELECT DATEFROMPARTS(@year, 1, 13)
UNION SELECT DATEFROMPARTS(@year, 2, 13)
UNION SELECT DATEFROMPARTS(@year, 3, 13)
... continue for all 12 months ...
)
SELECT
Thirteenth
FROM
cteThirteens
WHERE
((DATEPART(dw, Thirteenth) + @@DATEFIRST - 1) % 7) = 4
;```
NB: You need to take the `@@DATEFIRST` value into account, since it will alter the weekday numbers returned by the `DATEPART` function.
DATEPART (Transact-SQL) - SQL Server | Microsoft Docs[^]

Also note that your expected results are incorrect:
• 13th March 2020 was a Friday, not a Thursday, so it should not be included.
• 13th December 2020 was a Sunday, not a Thursday, so it should not be included.
• 13th August 2020 was a Thursday, so it's missing from your expected output.