Doubt with Getdate()?

Question

I have an SQL query that should bring me only records whose date is longer than the date of the day, but records with the current date are coming. Thanks!

   --não deveria mostrar a data de hoje
 select a.datavencimento  from TB_RECEBIMENTO a   
 left outer join   tb_plano_contas b  on  a.controleplano = b.controleplano               
 left outer join   tb_cliente_fornecedor d   on  a.controlecliente = d.controlecliente    
  where getdate() > a.datavencimento                                                 
     and a.statusregistro   = 'A' 
     and  b.tipododocumento = 'C' 
     and  a.controleempresa = '1' 
     order by a.datavencimento asc

Upshot:

2015-04-27 00:00:00.000
2015-04-27 00:00:00.000
2015-04-27 00:00:00.000
2015-04-28 00:00:00.000
2015-04-28 00:00:00.000
2015-04-28 00:00:00.000
2015-04-28 00:00:00.000
2015-04-28 00:00:00.000

Answer 1

You should disregard the hours to make this comparison. So:

DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

A tip is to create a function for this, to make the code clearer:

CREATE FUNCTION truncate_date (@data DATETIME)
RETURNS DATETIME
AS
BEGIN
  RETURN 
(
    DATEADD(dd, 0, DATEDIFF(dd, 0, @data))
)
END

Then your Query would be:

select a.datavencimento  from TB_RECEBIMENTO a   
left outer join   tb_plano_contas b  on  a.controleplano = b.controleplano               
left outer join   tb_cliente_fornecedor d   on  a.controlecliente = d.controlecliente    
where dbo.truncate_date(getdate()) > a.datavencimento
-- ou DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) > a.datavencimento
 and a.statusregistro   = 'A' 
 and  b.tipododocumento = 'C' 
 and  a.controleempresa = '1' 
 order by a.datavencimento asc

Answer 2

The reason why this is happening is that GetDate() returns a Datetime, beyond the date also returns the part of the Time for hours, minutes and seconds.

The recorded data is all with the Time to zero. When the comparison is made all today’s dates are smaller than getData().

I hope I made myself understood.

Answer 3

You are getting this result because the current time (caught by GETDATE()) is being considered (is a value of type DATETIME). Thus, today at noon is larger than today at midnight (which seems to be the time of all salaries), and today’s salaries meet the condition of the query. One of the possible solutions is to convert the return of GETDATE for the guy DATE (available from SQL Server 2008):

WHERE CONVERT(DATE, GETDATE()) > a.datavencimento        
-- etc.