title: MSSQL - Define a column name in SELECT statement then use that in WHERE clause
body: <p>is there any way to use defined column name in SELECT statement with WHERE clause?
  Here is my t-sql codes,</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Yours affectionately and thank you very much for your helps, already now.</p>
  
  <p><strong>Edited part of posted</strong></p>
  
  <pre><code>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
  </code></pre>