It is basically due to the sequence in which various clauses are evaluated/processed. In this specific case, WHERE is evaluated/processed before SELECT (hence the alias is not available at the time of evaluation/processing of WHERE clause) and ORDER BY is evaluated/processed after the SELECT (hence the alias is available at the time of evaluation/processing of ORDER BY).

Logical query processing evaluates FROM -> ON -> WHERE -> SELECT -> ORDER BY.

In query 1, database query engine wants Actual column name to process the where condition and it is not present, that's why it is failed. In query 2, query engine can use column alias to get the required information, hence it is working fine.

It's all about the way in which the clauses are evaluated. ORDER BY is evaluated after SELECT and hence, the database engine knows what the "date_only" alias means. WHERE is evaluated before SELECT, and therefore, column aliases do not work with WHERE clauses.

This is simple concept of query execution architechture.From the 11 layers of the Arch. the WHERE clause comes before the SELECT executes and ORDER BY clause executes after the SELECT.that's why "date_only" column identified by ORDER BY clasue but not by WHERE clause.

There is an interesting discussion on the very same topic in sql server quiz 2010. Slightly diff. Variation was asked by itzik as question 2.

In Logical query processing, 'Where' Clause is processed before 'Select' Clause, so while processing 'Where' Clause, it can not identify alias 'date_only' and will throw error. But 'Order By' is processed after 'Select' Clause, so it can identify by alias name and will not throw an error.