-1

I've got this kinda big query that is working as expected, now I want to make another query that returns only rows which have an ending date between today and 30 days from now.

What I've tried:

SELECT clientes.nome, docs.apolice, docs.nome_seguradora as seguradora, docs.nome_produto as ramo, comissoes.premio_liqdesc as premio_liquido, comissoes.premio_total as premio_total, 
docs.inicio_vigencia as inicio, docs.termino_vigencia as fim FROM growsystem.documentos docs INNER JOIN growsystem.comissoes comissoes ON docs.apolice = comissoes.apolice AND docs.situacao = 'Ativa' 
AND **DATEDIFF(GETDATE(), docs.termino_vigencia)** INNER JOIN growsystem.clientes clientes ON clientes.nome = comissoes.nome_cliente;

The ** only to highlight the part where I'm stuck at. If I remove it, the query works fine.

Where is the error in the syntax or logic?

Johnny Gregório
  • 1
  • 1
  • In this query, you aren't comparing `datediff` to anything. You probably want something like `DATEDIFF(GETDATE(), docs.termino_vigencia) < 30` or `DATEDIFF(GETDATE(), docs.termino_vigencia) between 0 and 30` – EdmCoff Aug 12 '22 at 15:24
  • 2
    @EdmCoff the two options you gave are not the same, the `BETWEEN` is equivalent to `>= 0 AND <=30`. Additionally, if `docs.termino_vigencia` is in the future, the `datediff` would return negative values – HoneyBadger Aug 12 '22 at 15:36
  • @honeybadger I thought it was pretty clear that they aren't the same (since the one would include negative numbers), but thanks for pointing out that `between` is inclusive of the end value (might not be obvious). I do not know the exact requirements and was attempting to show the basic idea behind using `datediff` for a comparison. – EdmCoff Aug 12 '22 at 15:43
  • @HoneyBadger Yes, I missed completely that the value would be negative if this case. Thanks. – EdmCoff Aug 12 '22 at 15:47
  • MySQL does not have getdate function search for MySQL getdate for alternatives – P.Salmon Aug 12 '22 at 15:48
  • Another possible issue if this really is MySql and that really is the exact code you're trying is that it should probably be `CURDATE()` not `GETDATE()` – EdmCoff Aug 12 '22 at 15:48

1 Answers1

0

I fixed it.

SELECT clientes.nome, docs.apolice, docs.nome_seguradora as seguradora, docs.nome_produto as ramo, comissoes.premio_liqdesc as premio_liquido, comissoes.premio_total as premio_total, 
docs.inicio_vigencia as inicio, docs.termino_vigencia as fim FROM growsystem.documentos docs INNER JOIN growsystem.comissoes comissoes ON docs.apolice = comissoes.apolice AND docs.situacao = 'Ativa' 
INNER JOIN growsystem.clientes clientes ON clientes.nome = comissoes.nome_cliente WHERE docs.termino_vigencia BETWEEN NOW() AND date_add(now(), INTERVAL 30 DAY);

As mentioned, I wasn't using the correct function. I switched from getdate() to now() (oops...). Also, filtered after every join, and used between instead of datediff. Datediff was hard to use because when comparing to something, it was returning a Boolean. Thanks for the tips.

Johnny Gregório
  • 1
  • 1