Order BY w/ functions on derived column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order BY w/ functions on derived column name

Author

Topic

elomon
Starting Member

37 Posts

Posted - 2004-06-09 : 12:47:35

Here's the query:
Select myName,CONVERT(varchar,empStDt,100) as EmployeeStartDate
from tblEmployees
order by EmployeeStartDate

this works but the ordering by confuses the AM & PM on the same days, so two people started same day, the one starting at 4PM is ordered before the one starting at 10AM, so I tried:

Select myName,CONVERT(varchar,empStDt,100) as EmployeeStartDate
from tblEmployees
order by Convert(smallDateTime,EmployeeStartDate)
and got : invalid column name EmployeeStartDate. Can I not run functions against the derived colums names?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-09 : 12:51:47

You can't use the alias in the ORDER BY. Just use the actual column name.

ORDER BY CONVERT(smalldatetime, empStDt)

Tara

elomon
Starting Member

37 Posts

Posted - 2004-06-09 : 13:01:51

That's what I figured, bummer.
Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-09 : 13:26:02

quote:

this works but the ordering by confuses the AM & PM on the same days, so two people started same day, the one starting at 4PM is ordered before the one starting at 10AM, so I tried:

Why don't you store the start date as a true datetime datatype? then you won't have this problem.

- Jeff

elomon
Starting Member

37 Posts

Posted - 2004-06-09 : 13:35:46

It is stored as a true datetime datatype - smalldatetime. I'm doing the convert for the style (100) i want to display.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-09 : 13:38:37

Style 100 is the default.

Does this give the correct results?:

Select myName,empStDt
from tblEmployees
order by empStDt

Tara

elomon
Starting Member

37 Posts

Posted - 2004-06-09 : 13:47:08

Maybe the default has been set differently on our SQL farms. With smalldatetime I get:
2004-06-01 09:32:45.000

With the convert I get:
Jun 1 2004 9:32AM

I want the month before the day & prefer the short monthname to a number. The query you posted works but I would have to:
Select convert(varchar,empStDt,100) as EmployeeStartDate -- for displaying
from employeestartdate
order by EmpStDt -- for ordering

Not a big deal, I was just hoping to re-use the derived column name, was hoping I could run a function on it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-09 : 13:50:38

Oops, I meant to say Style 100 is the default for CONVERT, so:

Select myName,CONVERT(varchar(50), empStDt)
from tblEmployees
order by empStDt

But, you've got it already.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources