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
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'.
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...).