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

Question

I'm a bit 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 that I get the error:

Invalid column name 'dCloseDate'.

Anyone know a way around that? I just want to do it to make my code more readable/maintainable.

Any advice as to why I shouldn't do it would also be appreciated :)

Answer 1

You can not use “on-the-fly” column names in SQL WHERE clause. (You can in the ORDER BY clause.) You have to 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 you shouldn't do it?

Having said that, you are performing a function against your column dStartDateTime which requires a table scan. Always perform functions on the other side, so that the value found can be tested against an index on dStartDateTime (datetime column).

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