getting invalid column error in SQL Server

Question

I have the following query:

The value in the TimeStamp columns is 201403240004 so the following function converts it in the format: 2014-03-24 00:04:00.000

SELECT CONVERT(DATETIME,
   SUBSTRING([TimeStamp],1,4)+'-'+SUBSTRING([TimeStamp],5,2)+'-'
  +SUBSTRING([TimeStamp],7,2)+' '+SUBSTRING([TimeStamp],9,2)+':'
  +SUBSTRING([TimeStamp],11,2)+':00.000') AS MessageDate
FROM Table
   WHERE MessageDate = '2014-03-24 00:04:00.000'

When I am trying to use MessageDate in WHERE clause, why do I get invalid column name error in sqlserver? How can I use the converted MessageDate in my WHERE clause?

SQL Server 2008. I am using SQL Server Management studio for running queries. — rocky, Apr 24 '14 at 22:37
@EricHauenstein I know, the query sounds [familiar to me](http://stackoverflow.com/a/23280693/3434221), but the naming here was rather ambiguous. — Tomas Pastircak, Apr 24 '14 at 22:40

jpw · Answer 1 · 2014-04-24T22:45:40.580

You can't use a computed column directly as theselectstatement is processed after the whereclause. One way of using the computed column is to wrap it in another select like this:

SELECT * FROM (
  SELECT CONVERT(DATETIME,
     SUBSTRING([TimeStamp],1,4)+'-'+SUBSTRING([TimeStamp],5,2)+'-'
    +SUBSTRING([TimeStamp],7,2)+' '+SUBSTRING([TimeStamp],9,2)+':'
    +SUBSTRING([TimeStamp],11,2)+':00.000') AS MessageDate
  FROM Table) innerQuery
WHERE MessageDate = '2014-03-24 00:04:00.000'

or you could use the full computation in the where clause.

Tomas Pastircak · Accepted Answer · 2014-04-24T22:43:14.140

You can't really use the column you have just defined. Your options are:

Explicitly rewrite the original query (I won't recommend that - too complicated, not too foolproof):

SELECT CONVERT(DATETIME,
   SUBSTRING([TimeStamp],1,4)+'-'+SUBSTRING([TimeStamp],5,2)+'-'
  +SUBSTRING([TimeStamp],7,2)+' '+SUBSTRING([TimeStamp],9,2)+':'
  +SUBSTRING([TimeStamp],11,2)+':00.000') AS MessageDate
FROM Table WHERE CONVERT(DATETIME,
   SUBSTRING([TimeStamp],1,4)+'-'+SUBSTRING([TimeStamp],5,2)+'-'
  +SUBSTRING([TimeStamp],7,2)+' '+SUBSTRING([TimeStamp],9,2)+':'
  +SUBSTRING([TimeStamp],11,2)+':00.000') = '2014-03-24 00:04:00.000'

Insert the data to temporary table (would be a good option if you need to access it more times)
My favorite option: Use a Common Table Expression:

;WITH data AS (SELECT CONVERT(DATETIME,
   SUBSTRING([TimeStamp],1,4)+'-'+SUBSTRING([TimeStamp],5,2)+'-'
  +SUBSTRING([TimeStamp],7,2)+' '+SUBSTRING([TimeStamp],9,2)+':'
  +SUBSTRING([TimeStamp],11,2)+':00.000') AS MessageDate
FROM Table
 ) SELECT * FROM data
   WHERE MessageDate = '2014-03-24 00:04:00.000'

What is `;WITH ` ? Is your statement follow the `SELECT` Query I have used? — rocky, Apr 24 '14 at 22:41
`;WITH` is a Common Table Expression, a really useful thing in MSSQL. I have improved the post now to include a [link](http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx) to some details about how to use CTE. — Tomas Pastircak, Apr 24 '14 at 22:45

getting invalid column error in SQL Server

2 Answers2