MSSQL - Define a column name in SELECT statement then use that in WHERE clause

Question

is there any way to use defined column name in SELECT statement with WHERE clause? Here is my t-sql codes,

SELECT CONVERT(DATETIME,(
    CASE WHEN Operator = 'T' THEN 
        (SUBSTRING(SendDate,7,4)+'-'+SUBSTRING(SendDate,4,2)+'-'+ 
        SUBSTRING(SendDate,1,2) + SUBSTRING(SendDate,11,9)) 
    ELSE 
        RecivedSMS.SendDate 
    END)) AS DefinedDate 
WHERE DATEPART(wk, DefinedDate) =  52

Yours affectionately and thank you very much for your helps, already now.

Edited part of posted

CREATE TABLE #TEMP
(
DateField DATETIME,
NumberField VARCHAR(10),
Sender VARCHAR(255)
)

INSERT #TEMP
SELECT DISTINCT(
CONVERT(DATETIME, ( 
                    CASE WHEN Operator = 'T' 
                    THEN (  SUBSTRING( SendDate, 7, 4 ) + '-'
                            + SUBSTRING( SendDate, 4, 2 ) + '-'
                            + SUBSTRING( SendDate, 1, 2 )
                            + SUBSTRING( SendDate, 11, 9 )
                         ) 
                    ELSE SendDate 
                    END))) AS Table1.DateField,

SUBSTRING(Table1.Message,7,7) AS NumberField

FROM Table1
INNER JOIN 
Table2 ON Table1.Sender = Table2.PhoneNumber,
(
          SELECT  CONVERT(DATETIME, ( 
                    CASE WHEN Operator = 'T' 
                    THEN (  SUBSTRING( SendDate, 7, 4 ) + '-'
                            + SUBSTRING( SendDate, 4, 2 ) + '-'
                            + SUBSTRING( SendDate, 1, 2 )
                            + SUBSTRING( SendDate, 11, 9 )
                         ) 
                    ELSE SendDate 
                    END)) AS DefinedDate 
          FROM    Table1 WHERE Table1.Operator IS NOT NULL
        ) q  
WHERE Operator IS NOT NULL AND SUBSTRING(Table1.Message,1,6) = 'TREE ST'  AND DATEPART( wk, q.DefinedDate ) =  52 AND DATEPART(year, q.DefinedDate ) = 2010


SELECT *  FROM #TEMP ORDER BY NumberField

DROP TABLE #TEMP

Answer 1

Not directly but you could wrap it in a subselect.

SQL Statement

    SELECT  *
    FROM    (
              SELECT  CONVERT(DATETIME, ( 
                        CASE WHEN Operator = 'T' 
                        THEN (  SUBSTRING( SendDate, 7, 4 ) + '-'
                                + SUBSTRING( SendDate, 4, 2 ) + '-'
                                + SUBSTRING( SendDate, 1, 2 )
                                + SUBSTRING( SendDate, 11, 9 )
                             ) 
                        ELSE RecivedSMS.SendDate 
                        END)) AS DefinedDate 
              FROM    YourTable
            ) q          
    WHERE   DATEPART( wk, DefinedDate ) =  52

---

update

I believe following would be equivalent to what you wrote:

SELECT  q.DefinedDate
        , q.NumberField
FROM    (
          SELECT  Sender
                  , NumberField = SUBSTRING(Table1.Message, 7, 7) 
                  , Operator
                  , CONVERT(
                      DATETIME
                      , ( 
                          CASE WHEN Operator = 'T' 
                          THEN SUBSTRING( SendDate, 7, 4 ) + '-'
                               + SUBSTRING( SendDate, 4, 2 ) + '-'
                               + SUBSTRING( SendDate, 1, 2 )
                               + SUBSTRING( SendDate, 11, 9 )
                          ELSE SendDate 
                          END )) AS DefinedDate 
          FROM    Table1 WHERE Table1.Operator IS NOT NULL
        ) q  
        INNER JOIN Table2 ON Table2.PhoneNumber = Tabl1.Sender
WHERE   q.NumberField LIKE 'TREE ST%'  
        AND DATEPART( wk, q.DefinedDate ) =  52 
        AND DATEPART( year, q.DefinedDate ) = 2010