Can you use column names on the fly in SQL WHERE clasue?

Question

I'm a little rusty with my SQL.

I thought I could do something like this:

SELECT *, DATEADD(d, 1 ,dStartDateTime) dCloseDate
FROM EventItem 
WHERE dCloseDate > '1990-01-01 07:00:00.000'

But when I do this, I get an error:

Invalid column name 'dCloseDate'.

Does anyone know about this? I just want to do this to make my code more readable / supported.

Any advice on why I shouldn't do this would also be appreciated :)

Answer 1

You cannot use column names on the fly in an SQL WHERE clause. (You can in the sentence ORDER BY.) You must subquery it or repeat the expression

SELECT * FROM (
SELECT *, DATEADD(d, 1 ,dStartDateTime) dCloseDate
FROM EventItem
) SUBQ
WHERE dCloseDate > '1990-01-01 07:00:00.000'

-or -

SELECT *, DATEADD(d, 1 ,dStartDateTime) dCloseDate
FROM EventItem 
WHERE DATEADD(d, 1 ,dStartDateTime) > '1990-01-01 07:00:00.000'

Why shouldn't you do this?

, dStartDateTime, . , dStartDateTime (datetime).

SELECT *, DATEADD(d, 1 ,dStartDateTime) dCloseDate
FROM EventItem 
WHERE dStartDateTime > DATEADD(d, -1 ,'1990-01-01 07:00:00.000')

Answer 2

, , AS. WHERE:

SELECT *, DATEADD(d, 1 ,dStartDateTime) AS dCloseDate
FROM EventItem 
WHERE DATEADD(d, 1 ,dStartDateTime) > '1990-01-01 07:00:00.000'

. .

Answer 3

Sorry, but you cannot reference column aliases (dCloseDate, in your example) where clauses are (or group bytes or orders). It would be nice if you / we could, but MS SQL just doesn't support this.