Getting SQL server to recognise a date column
I receive this error "Invalid column name 'Today'"


You can't reference the Today expression in the WHERE clause because the WHERE clause is evaluated before the SELECT clause according to the logical order of query processing. 

Since your desired type is date, it would be better to use CAST or CONVERT rather than the DATEADD...DATEDIFF ugliness (common before SQL Server 2008 introduced the date datatype). Also, you need to use DATEADD to calculate yesterday's date instead of a subtraction operator.

The example below uses these techniques to get the orders from yesterday:

SELECT
      order_id
    , user_id
    , date_created
    , order_value
    , city_id
    , CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;