SQL alias gives invalid column name

Question

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()?

Answer 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' 

Answer 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.

Answer 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....