Problem in field comparison with date and time

Yes, it causes data return problem. In your example, data between '23 / 11/2016 00:01 'and '23 / 11/2016 23:59' will not be returned in the query.

I tackle this problem in my work environment and I usually solve it in two different ways:

Example: Search all records on 11/23/2016.

1 - First form

SELECT * FROM MOVIMENTOS
WHERE movData BETWEEN '23/11/2016' AND '23/11/2016 23:59:59'

2 - Second way

SELECT * FROM MOVIMENTOS
WHERE movData >= '23/11/2016' AND movData < '24/11/2016'

or

SELECT * FROM MOVIMENTOS
WHERE movData >= '23/11/2016' AND movData < DATEADD('d', 1, '23/11/2016')

If the field is of type DATA and has time, it is imperative to place the time in the query filter too, under penalty of the returned data set being incorrect.

@itasouza, the form you use is reliable because you previously converted from datetime to date . That is, the comparison will always be between two operands of type date . It does not cause any problem in returning the data.

-- código original do itasouza
select  top(5)
   p.codinterno, 
   c.dtHrProcesso --pega um horário fixo 
   FROM  TABELA p
   INNER JOIN  TABELAC c on c.idProcesso = p.idProcesso
   WHERE CAST(c.dtHrProcesso AS DATE) = '2016-11-23'

Note that if in a given row the value of the c.dtHrProcesso column is "2016-11-23 05: 35: 33.690", we will then have the following comparison:

 Cast('2016-11-23 05:35:33.690' as date) = '2016-11-23'

which is the same as

 '2016-11-23' = '2016-11-23'

For those who are in doubt if the original code proposed by @itasouza is correct, just run the following code.

-- código #1
declare @TABELAC table (Seq int identity, dtHrProcesso datetime);

INSERT into @TABELAC (dtHrProcesso) values
  (convert(datetime, '2016-11-23 03:55:18', 120)), 
  (convert(datetime, '2016-11-22 02:00:22', 120)), 
  (convert(datetime, '2016-11-24 12:44:56', 120)), 
  (convert(datetime, '23/11/2016', 103)), 
  (convert(datetime, '2016-11-23 23:59:59', 120));

SELECT * from @TABELAC;  

SELECT Seq, dtHrProcesso
  from @TABELAC as C
  where Cast(dtHrProcesso as date) = '2016-11-23';

It would be even better if you did something similar to:

-- código #2
declare @DataPesq date;
set @DataPesq= Convert(date, '23/11/2016', 103);

SELECT top (5) p.codinterno, c.dtHrProcesso
  from TABELA as p
       inner join TABELAC as c on c.idProcesso = p.idProcesso
  where CAST(c.dtHrProcesso AS DATE) = @DataPesq;

Another option:

-- código #3
declare @DataPesq date;
set @DataPesq= Convert(date, '23/11/2016', 103);  -- formato dd/mm/aaaa

-- variáveis internas para delimitação de período
declare @DataInic datetime, @DataFin datetime;
set @DataInic= @DataPesq;
set @DataFin= DateAdd(ms, -3, DateAdd(day, +1, DataPesq));

--
SELECT top (5) p.codinterno, c.dtHrProcesso
  from TABELA as p
       inner join TABELAC as c on c.idProcesso = p.idProcesso
  where c.dtHrProcesso between @DataInic and @DataFin;

When you want to ensure that the code is sargable , you should avoid applying functions to table columns in the WHERE clause. However, the form

 Cast(coluna_datetime as date) = ...

is one of the exceptions. See Make more functions SARGable .

Yes

Verify that

SELECT CASE WHEN CAST( '2016-11-23 05:35:33.690' AS DATE ) = '2016-11-23' THEN 'TRUE' ELSE 'FALSE' END

returns true and

SELECT CASE WHEN '2016-11-23 05:35:33.690' = '2016-11-23' THEN 'TRUE' ELSE 'FALSE' END

returns false