Everyone, good afternoon! I'm having trouble with the query below. I want you to bring all the tasks only when the maturity (field) of them is equal to the month after the opening date (tardata). However, nothing came up in the query.
select *
from Tarefa
where TarID = 173151
and MONTH(TarVencimento) = DATEADD(MONTH,1,TarData)
I think it's more or less what you need, follow my test:
CREATE TABLE teste(tarId INT, TarVencimento DATE);
INSERT INTO teste VALUES(173151,'2017-12-01'),(173151,'2018-01-01'),(173151,'2018-02-01');
SELECT
*
FROM
teste a
INNER JOIN
(
select
tarId,
TarVencimento
from teste
where tarId = 173151
) as aux
ON a.tarId = aux.tarId
WHERE month(aux.TarVencimento) = month(a.TarVencimento + interval 1 month)
Result:
Invoice 1 is referenced to 2 (Later Month) and Invoice 2 is 3 (Later Month)
If you want to pick only 1 you can sort by ASC TarVencimento and add a LIMIT 1
Only contextualizing the @Rovann response
DATEADD returns a date. The MONTH returns an integer that represents the month of the date.
Soon your clausulá where MONTH(TarVencimento) = DATEADD(MONTH,1,TarData) does not seem to make sense.
Your where clause becomes more meaningful, changing it to MONTH(TarVencimento) = MONTH(DATEADD(MONTH,1,TarData)) . Where you add 1 month to the TarData field, take the month and finally compare it to the TarVencimento month.
Seeing your question, I see that I stopped in time with SQL Server studies, because I still use # to get the month in the date fields, getting something like: DATEPART(MONTH, TarVencimento) = DATEPART(MONTH, DATEADD(MONTH,1,TarData)) , which is not unsatisfactory either, since DATEPART returns an integer that represents the datepart of the specified date.
If I get it right:?!
select *
from Tarefa
where TarID = 173151
and MONTH(TarVencimento) = MONTH(DATEADD(MONTH,1,TarData))