Order by with datetime field - SQL Server Forums

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 with datetime field

Author

Topic

scozzese
Starting Member

12 Posts

Posted - 2002-09-25 : 11:19:00

I've the following query:
****
Select date.AS400 AS DATE from tableAS400
UNION
Select format(date.SQL,'yyyymmdd') AS DATE FROM tableSQL
ORDER BY YEAR(DATE), MONTH (DATE)
****

date.AS400 is decimal size 8 (es.20020925)
date.SQL is datetime (es.25/09/2002)

With 'ORDER BY' the query doesn't work, why?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-25 : 12:09:17

try
select Date
from
(
Select date.AS400 AS DATE from tableAS400
UNION
Select convert(varchar(8),date.SQL,112) AS DATE FROM tableSQL
) as a
ORDER BY YEAR(DATE), MONTH (DATE)

It's to do with the way the server resolves the objects.
I've also replaced the format with a convert - up to you if it works

And you have format yyyymmdd so you can just order by DATE which will work too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 09/25/2002 12:10:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources