Find rows that have yesterdays date -- Invalid column?

Question

Goal: I am trying to get the rows in a table that have yesterdays date (but the original column is in datetime).

What I've tried: I was able to figure out how to get yesterdays date from https://learnsql.com/cookbook/how-to-get-yesterdays-date-in-t-sql/

But I also had to cast the DateTime from the table to date to match that yesterdays date was in Date format. So the SO articles like SQL statement to select all rows from previous day did not work.

When I try to find the matching rows from my DB Table, it says:

Invalid column name 'YesterdayDate'

SQL:

SELECT cast(DateEnded as date) AS YesterdayDate
FROM [dbo].[V]
WHERE YesterdayDate = DATEADD(DAY, -1, CAST(GETDATE() AS date));

How can I correctly do this?

Answer 1

In fact don't do it that way. You want to avoid calling functions on columns in your WHERE clause as they can make the query unsargable i.e. unable to use indexes. Instead use the actual column, and rather than converting to a date use a datetime window of the previous day.

SELECT CAST(DateEnded AS date) AS YesterdayDate
FROM [dbo].[V]
WHERE DateEnded >= DATEADD(DAY, -1, CAST(GETDATE() AS date))
AND DateEnded < CAST(GETDATE() AS date);