Where clause for aliased column

Question

I have a query which parses a string values from a column:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1 

When I do:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1.timesent != '0'

I get:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'timesent'.

Any ideas why?

Answer 1

You can't reference an alias in the WHERE clause. You'd have to do something like this:

select left(DateTimeSent, 17) as timesent
from allog_07_11_e
where left(DateTimeSent, 17) <> '0'

Likewise, you could also use a subquery:

select *
from
(
    select left(DateTimeSent, 17) as timesent
    from allog_07_11_e
) a
where timesent <> '0'

Answer 2

You CAN reference an alias if you define it with CROSS APPLY:

SELECT timesent 
FROM allog_07_11_e query1
CROSS APPLY(SELECT left(DateTimeSent, 17)) CxA(TimeSent)
WHERE timesent <> '0'