Invalid Column Name error,

Hi,

I'm having trouble with the following query.

select convert(datetime, convert(int, audit_timestamp - 0.5)) as auditdate, database_name, sum(FileSize) as FileSize, sum(fileUsed) as FileUsed, sum(FileFree) as FileFree
from tbl_dbSize
where auditdate > getDate() -7
and lower(server_name) = 'xxx'
group by auditdate, database_name

Basically what I am trying to do is convert my records in the select statement (as I don't want to update the actual data) which were recorded on the same day (however with different times, i.e. 27/12/2003 00:01:03 , 27/12/2003 00:01:03) to be the same (i.e. 27/12/2003 00:00:00).

I keep getting the error,

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'auditdate'.

Any help appreciated.First, use this to truncate your datetime values:

cast(Convert(varchar(10), audit_timestamp, 120) as datetime) as auditdate

Second, you can't reference AuditDate by name; you have to reference it by the formula:

where cast(Convert(varchar(10), audit_timestamp, 120) as datetime) > getDate() -7
.
.
.
group by cast(Convert(varchar(10), audit_timestamp, 120) as datetime), database_name

It would be nice if TSQL allowed you to define the formula once and then refer to it by name, but the name isn't assigned until the query is completed and so is not available to the parser. (The exception is if you query is a subquery of another query, but that is another discussion...).

blindman|||reference to a column alias is allowed only in order by clause|||Thanks for the help.

It's working now!