Get data from yesterday

Question

Hi,

I want to get data against yesterday

data table.[full_date]
2000-01-11 00:00:00.000

How would I say where full_date = yesterday?

Thanks

Chris

Answer 1

Yesterday is today - 1 day

WHERE full_date = DATEADD(day, -1, GETDATE())

However chances are your column is actually a DATETIME and therefore the equality check will fail because the timestamps are different so you need to filter out that as well. Different ways of doing it. If the full_date column has all the times set to midnight then you need only remove the timestamp from yesterday.

WHERE full_date = DATEADD(day, -1, CAST(GETDATE() AS DATE))

However if your full_date also has timestamps then a date diff is needed. Perhaps something like this (not fully tested).

WHERE DATEDIFF(day, full_date, DATEADD(day, -1, GETDATE())) = 0

Answer 2

Hi @Christopher Jack
There are several methods to help you get yesterday's data:
Method 1: Use DATEDIFF function which returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

SELECT * FROM data_table  
WHERE DATEDIFF(day,full_date,GETDATE()) = 1  

Method 2: Use DATEADD function which adds a number (a signed integer) to a datepart of an input date, and returns a modified date/time value.
Note that: If full_date is the datatype of Datetime, then you need to add CAST to convert it to the DATE datatype.

SELECT * FROM data_table  
WHERE CAST(full_date AS date) = CAST(DATEADD(day,-1,GETDATE()) AS date)  

Method 3: Suppose today is March 10th, then you need WHERE full_date>='2022-03-09 00:00:00.000' AND full_date <'2022-03-10 00:00:00.000' ,check this :

--IF full_date column is 'Datetime' datatype  
SELECT * FROM data_table  
WHERE full_date >=DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0)  --'2022-03-09 00:00:00.000'  
  AND full_date < DATEADD(day, DATEDIFF(day,0,GETDATE()),0)  --'2022-03-10 00:00:00.000'  
  
--IF full_date column is not 'Datetime' datatype  
SELECT * FROM data_table  
WHERE CAST(full_date AS datetime) >=DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0)   
  AND CAST(full_date AS datetime) < DATEADD(day, DATEDIFF(day,0,GETDATE()),0)   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

select * from myTable where [full_date]>=cast(dateadd(day,-1, CURRENT_TIMESTAMP) as date)
and [full_date] < cast(current_timestamp as date)