Recover data only from the previous day in a table in SQL Server

Question

I need to retrieve data from a table that has date referenced only the previous day, I am trying to do with the query below but I am not getting:

SELECT 
    Log.ValorEntrada, Log.DataHoraEvento, Log.NumeroEntrada 
FROM 
    Log 
WHERE 
    Log.DataHoraEvento = (GETDATE()-1)

How can I get this result?

Possible duplicate of [SQL statement to select all rows from previous day](https://stackoverflow.com/questions/1503298/sql-statement-to-select-all-rows-from-previous-day) — underscore_d, Aug 01 '17 at 15:06

score 3 · Accepted Answer · answered Aug 01 '17 at 15:05

In SQL Server, GETDATE() has a time component. I would recommend:

WHERE Log.DataHoraEvento >= CAST(GETDATE()-1 as DATE) AND
      Log.DataHoraEvento < CAST(GETDATE() as DATE)

This condition is "sargable", meaning that an index can be used. The following also is:

WHERE CONVERT(DATE, Log.DataHoraEvento) >= CONVERT(DATE, GETDATE())

Almost all functions prevent the use of indexes, but conversion/casting to a date is an exception.

Finally, if you don't care about indexes, you can also write this as:

WHERE DATEDIFF(day, Log.DataHoraEvento, GETDATE()) = 1

DATEDIFF() with day as the first argument counts the number of "day" boundaries between the two date/times. Everything that happened yesterday has exactly one date boundary.

score 1 · Answer 2 · answered Aug 01 '17 at 15:06

If DataHoraEvento is a DATETIME, its likely that it has the full time, hence GETDATE()-1 isn't getting any matches. You should search for a range like this:

SELECT L.ValorEntrada, L.DataHoraEvento, L.NumeroEntrada 
FROM dbo.[Log] L
WHERE L.DataHoraEvento >= CONVERT(DATE,DATEADD(DAY,-1,GETDATE()))
AND L.DataHoraEvento < CONVERT(DATE,GETDATE());

score 1 · Answer 3 · answered Aug 01 '17 at 15:20

SELECT Log.ValorEntrada, Log.DataHoraEvento, Log.NumeroEntrada 
FROM   Log 
WHERE  Log.DataHoraEvento >= DATEADD(dd,DATEDIFF(dd,1,GETDATE()),0) 
AND    Log.DataHoraEvento < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

You should also use SYSDATETIME() (if you on SQL Server 2008+) instead of GETDATE() as this gives you datetime2(7) precision.

score 0 · Answer 4 · answered Aug 01 '17 at 15:06

0

You can try this :

MEMBER BETWEEN DATEADD(day, -2, GETDATE()) AND DATEADD(day, -1, GETDATE())

answered Aug 01 '17 at 15:06

Akshay Prabhakar

430
4
13

Recover data only from the previous day in a table in SQL Server

4 Answers4