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

Solution

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

Comments

[Kerberos]

Hi Lieven, thank you. what does "q" mean in your suggest? it is table name which is belong to "from statment", is'nt it?

[Lieven Keersmaekers]

q is indeed merely a table alias needed by SQL Server to reference the results of the inner select. Any name would do. It's something I've picked up from Quassnoi so I don't have to think about what name to use and stay focused on writing the query.

[Lamak]

Its an alias for the table that you obtain from the SELECT - FROM statement. You need to name your sub select in order to use it.

[Kerberos]

Hi again. I implemented your suggestion in project. But i think i could'nt implement right. I edited my post with rest of codes. Could you suggest me again?

[Kerberos]

If i don't use DISTINCT statement in first SELECT statement, so many rows efected in spite of one or two records.