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

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

Anyon开发者_运维知识库e 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 :)

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')

Unfortunately you can't use the name you gave in the AS clause. Instead you have to repeat the expression in the WHERE clause:

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

Which doesn't add to the readability or maintainability. But it is the only way top do it.

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