Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    reference to a column alias is allowed only in order by clause

  4. #4
    Join Date
    Aug 2003
    Posts
    11
    Thanks for the help.

    It's working now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •