Sql - "ORDER BY Date" & Date Format

Commissar

Can anyone explain why

"SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) AS Date FROM (SELECT * FROM table1 WHERE xyz) A, (SELECT * FROM table1 WHERE wxy) B WHERE A.ID = B.ID ORDER BY Date"

won't order the dates correctly when

"SELECT Date FROM table1 ORDER BY Date"

will?

Am I correct in assuming the format should have no bearing on date Functions? Presumably the subqueries are responsible.

Edit: Thanks for pointing that out Bonkey. This should make a bit more sense now.

bonkey

Commissar wrote:

Can anyone explain why "SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) FROM table1 AS Date" won't order the dates correctly when "SELECT Date FROM table1 ORDER BY Date" will?

I'm guessing its because the first select statement doesn't have an ORDER BY in it.

Beano

Let me get this straight. You have a field in a table called date. You select
DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) and alias this as a field also called date. Then you do an order by on the Date field. I'm assuming you think it should be the date field in the table that it uses for the order by? It wont. In this case it will use the field in the select list that isnt in date order. You need to either alias the field in the select list to something other than Date or change what the selected field is returning so that it is in date order. To do this use DATENAME(yy, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(DD, Date) using ddmmyy will not return the values in date order, yymmdd will.

Commissar

Thanks Beano. That's certainly moved me in the right direction.

bonkey

One other thing....you're using a 2-digit year in there.

So if you have two dates : Dec 31 1999 and Jan 01 2000, they get re-written as :

31 12 99 and 01 01 00

These are then sorted alphabetically (cause they're string values), and cause thats the field that you've specified as your sort.
The sort order will come back with the 1999 record *after* the 2000 record.

If you sorted on the original date fields, that couldn't happen.

Your best bet is possibly to change your ORDER BY to be either a.Date or B.Date, which will reference the field in the source "tables", rather than Date, which will be interpreted as the aliased output field.

bonkey

Incidentally, I'd be inclined to get rid of the inner select statements, and write the query more like hte following:

SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) AS Date
FROM table1 A, table1 B
WHERE A.ID = B.ID
AND xyz (referencing fields as A.fieldname)
AND wxy (referending fields as B.fieldname)
ORDER BY Date"

or

SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) AS Date
FROM table1 A
INNER JOIN table1 B
ON A.ID = B.ID
WHERE xyz (referencing fields as A.fieldname)
AND wxy (referending fields as B.fieldname)
ORDER BY Date"

In both cases, I'd expect to have to change the field inside the DATENAME function calls to either A.Date or B.Date, and presumably the same in the ORDER BY

amen

you should avoid columns with names such as Date