Error while searching for date greater than 1 year DATADIFF() and DATAADD() function

Asked

Viewed 45 times

0

Hello I need to perform a select in a database where the dataCompra is longer than one year. But I need to use a clause in my Where and when I try to use the function DATEDIFF() and DATEADD() so it does not return anything. Is there any limitation in these functions or my select that is incorrect?

select tb_Pedido.idPedido, 
        numeroPedido,dataCompra 
   from tb_Pedido, tb_NotaFiscal
     where DATEDIFF(dd,dataCompra, getdate()) > 366 AND
       tb_Pedido.idPedido = tb_NotaFiscal.idPedido AND
       tb_NotaFiscal.ARMAZENADO =0;

With dateadd()

select tb_Pedido.idPedido,numeroPedido,dataCompra  
  from tb_Pedido, tb_NotaFiscal
   where  
      tb_Pedido.dataCompra <= 
      DATEADD(yyyy,-1,getdate())
      AND tb_Pedido.idPedido = tb_NotaFiscal.idPedido AND
      tb_NotaFiscal.ARMAZENADO =0;

But when I only use Where without the clause and it works, but I need the and to do a check.

  • checked if the data really matches? uses of functions are correct

1 answer

0


The difference between the two methods is that in the first it uses difference in days, and in the second returns a date/time at the age of one.
In leap years there is a difference in the actual criterion.
On the other hand Getdate() also returns the time.

select DateDiff(dd,'2017-09-28','2018-09-28') 

365

Select DateAdd(yyyy,-1,getdate()) 

'2017-09-28 10:09:00'

Browser other questions tagged

You are not signed in. Login or sign up in order to post.