SQL Server – How to Get SQL Server to Recognize a Date Column

aliasdatesql server

I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.

My table looks like this

orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)

I have used this code to get todays date in the table – 

Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders

This works fine but when I try attempt a where clause below this 

Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;

I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?

Thanks in advance

Best Answer

Your query doesn't work because of the way queries are logically processed.

You need to either use a common table expression:

WITH c
    AS
     (
         SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
         FROM   orders
     )
SELECT *
FROM   c
WHERE  c.Today - 1 = date_created;

Or you could use a derived table.

SELECT *
FROM   (   SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
           FROM   orders ) AS c
WHERE  c.Today - 1 = date_created;

Neither one is "better".

Related Question