MSSQL. Define the column name in the SELECT statement, then use this in the WHERE clause.

Question

Is there a way to use a specific column name in a SELECT statement with a WHERE clause? Here are 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 

Sincerely, and thank you for your help, right now.

Edited part of publication

 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 can wrap it in a subquery.

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 the following will 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