5

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

  • 1
    Dan shows a much smoother way to convert GETDATE() (well, any date, really) to date: CONVERT(date, GETDATE()). Also I strongly recommend avoiding shorthand (you type dd; why not just type day?) and Today-1 (this will break with newer date/time types). See this and this. – Aaron Bertrand Dec 1 '18 at 14:11
  • Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future. – RustyRyan Dec 1 '18 at 14:38
  • No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness. – Aaron Bertrand Dec 1 '18 at 15:16
4

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".

5

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;
2

As others have indicated you're not able to use a calculation done in the SELECT clause in the WHERE clause due to the order of operations.

However, a nifty workaround I use from time to time is to do some calculations within a CROSS APPLY, and then those can be referenced in SELECT and WHERE. It is a helpful technique to avoid repeating code.

SELECT 
    A.*,
    S1.Today
FROM orders A
CROSS APPLY 
    (
        SELECT
            CAST(GETDATE() AS DATE) Today
    ) S1
WHERE   DATEADD(DAY, -1, S1.Today) = A.date_created;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.