Field comparison problem with date and time

Question

I have a query where I noticed that one of the fields used in the comparison brings date and time, being compared with the current date of the day without time, this can cause problems in the return of the data:?

ex:

      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' -- comando CAST(GETDATE() AS DATE)

Shopping : 2016-11-23 05:35:33.690 = '2016-11-23' The best would be to use :

WHERE CONVERT(VARCHAR(10), CAST(c.dtHrProcesso AS DATETIME), 120) = '2016-11-23'

Or has no influence on the outcome?

Answer 1

@itasouza, the form you use is reliable, because it has previously converted datetime for date. That is, the comparison will always be between two operands of the type date. Causes no problem in returning 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 row the value of the column c.dtHrProcesses 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;

Other 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 make sure the code is sargable, should avoid applying functions to table columns in the clause WHERE. However, the form

 Cast(coluna_datetime as date) = ...

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

Answer 2

Yes

Check 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

false return

Answer 3

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

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

Example: Search all records for the day 23/11/2016.

1 - First form

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

2 - Second form

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 put the time in the query filter also, under feathers of the returned data set be incorrect.