Order converted dates in union query

I have the following as part of a union query:

CONVERT(CHAR(8), r.RRDate, 1) AS [Date]

I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.

Thanks,

Dave

Do you really require UNION operator? If the results of each SELECT statement in the UNION is distinct then use UNION ALL. This will also provide better performance since it doesn't do the duplicate elimination step. And if you use UNION ALL then you can use the column name "r.RRDate" in the ORDER BY clause. If you need to use UNION then only way is to specify the column in the SELECT list also if you want it in the ORDER BY clause. Lastly, is there any reason for your to format the date in the query itself. It is usually unnecessary work to do this on the server-side. It is best to send the date value as is and format on the client. Alternatively, you can use a style which is universal and will preserve sorting for example like the ISO unseparated date format (style 112: YYYYMMDD) or ISO 8601 datetime format (style 126: YYYY-MM-DDThh:mm:ss.nnn). Using language dependent style format is always confusing and can cause errors when you try to use it as is in a different system that has a different language setting for example.