The queries are behaving correctly.

Any alias names assigned in the SELECT list come into existence more or less
right before the ORDER BY clause is executed (which is the last step in the
query execution process).  These are the names that the ORDER BY cursor
sees.  Thus, in your second query, the ORDER BY clause sees a column named
'DateTime' (bad column name, BTW ;-) which is the name of the column which
contains the CONVERT expression.

Note that if your queries had WHERE clauses (or some other, e.g. JOIN
expressions) which referred to the 'DateTime' column name, they would be
referring to the underlying table data and not the result of the CONVERT
expression since alias names don't come into existence until after those
clauses are executed.
--
Jeff Mason              Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Michael Metz [mailto:mjmetz@q...]
Sent: Thursday, February 13, 2003 2:42 PM
To: sql language
Subject: [sql_language] Sorting behavior


I'm not sure is this is known behavior or not but could anyone explain
why this is occuring. As you can see by the statement below I am sorting
two values that were converted from datetime to char. The 1st query
orders by the original datetime values as expected, but the 2nd query
orders by the converted char values even though ORDER BY T.DateTime is
stated. The 3rd orders by date. Is this because the 'AS DateTime in the
2nd query is treated as T.DateTime in the order by?

Thank you.

set nocount on

declare @Test table (
DateTime datetime
)

insert into @Test values ('Dec 31, 1999')
insert into @Test values ('Jan 01, 2000')

--*1st*
select convert(char(10), T.DateTime, 1)
from @Test as T
order by T.DateTime
----------Results
12/31/99
01/01/00

--*2nd*
select convert(char(10), T.DateTime, 1) as DateTime
from @Test as T
order by T.DateTime
----------Results
01/01/00
12/31/99


--*3rd*
select convert(char(10), T.DateTime, 1) as DateTimeX
from @Test as T
order by T.DateTime

----------Results
12/31/99
01/01/00