SQL alias gives invalid column name

问题:

Using the following SQL expression but I'm getting an error.

select
  CampaignCustomer.CampaignCustomerID,
  convert(varchar, CampaignCustomer.ModifiedDate, 111) as startdate,
  CampaignCustomer.CampaignID,
  CampaignCustomer.CampaignCallStatusID,
  CampaignCustomer.UserID,
  CampaignCustomerSale.Value,
  Users.Name
from CampaignCustomer
  inner join CampaignCustomerSale
    on CampaignCustomer.CampaignCustomerID = CampaignCustomerSale.CampaignCustomerID
  inner join Users
    on CampaignCustomer.UserID = Users.UserID
where
  CampaignCustomer.CampaignCallStatusID = 21
  and CampaignCustomer.startdate = '2011/11/22'      <------- THIS
order by
  startdate desc,
  Users.Name asc

Error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'startdate'.

I can't recognize my alias name startdate in the WHERE clause, but it can in my ORDER BY clause. What's wrong?

Edit:
And no, it is not possible for me to change the datatype to date instead of datetime. The time is needed elsewhere. But in this case, I need only to get all posts on a specific date and I really don't care about what time of the date the modifieddate is :)

Maybe another method is needed instead of convert()?

回答1:

You can't use column alias in WHERE clause.

---

Change it to:

where
  CampaignCustomer.CampaignCallStatusID = 21
  and convert(varchar, CampaignCustomer.ModifiedDate, 111) = '2011/11/22' 

回答2:

Do this:

select 
  CampaignCustomer.CampaignCustomerID, 
  convert(varchar, CampaignCustomer.ModifiedDate, 111) as startdate, 
  CampaignCustomer.CampaignID, 
  CampaignCustomer.CampaignCallStatusID, 
  CampaignCustomer.UserID, 
  CampaignCustomerSale.Value, 
  Users.Name 
from CampaignCustomer 
  inner join CampaignCustomerSale 
    on CampaignCustomer.CampaignCustomerID = CampaignCustomerSale.CampaignCustomerID 
  inner join Users 
    on CampaignCustomer.UserID = Users.UserID 
where 
  CampaignCustomer.CampaignCallStatusID = 21 
  and convert(varchar, CampaignCustomer.ModifiedDate, 111) = '2011/11/22'
order by 
  startdate desc, 
  Users.Name asc 

You need to put in your where clause no aliases, and in the above query I replaced your alias with what it represents.

回答3:

You didn't mention what version of SQL Server you're using - but if you're on 2008 or newer, you could use:

where
  CampaignCustomer.CampaignCallStatusID = 21
  and CAST(CampaignCustomer.ModifiedDate AS DATE) = '20111122'

You could cast it to a DATE - just for this comparison.

Also: I would recommend to always use the ISO-8601 standard format of representing a date if you need to compare a date to string - ISO-8601 defines a date as YYYYMMDD and is the only format in SQL Server that will always work - no matter what language/regional settings you have. Any other string representation of a date is always subject to settings in your SQL Server - it might work for you, but I bet for someone else, it will break....