Hi Everybody!!!!
There is a weird sorting behaviour in regards of dates. I have the following select statement:
SELECT inInventoryUnit.InvUnitTracUnit 'Pallet',
LTrim(Convert(varchar(14), inLot.MfgDate, 101)) 'MfgDate'
FROM inInventoryUnit
Left Join inLot on ((inInventoryUnit.InvUnitLotObjID = inLot.ObjectID))
Order by inLot.MfgDate ASC, inInventoryUnit.InvUnitTracUnit
...well, the original statement was actually more complex, but this is good enough...
So, inLot.MfgDate is a typical datetime, and InvUnitTracUnit is a varchar(30). There were only two records: One from December 2006 and one for January 2007.
The symptoms are: If I ask the query to sort the data by ascending date, it will sort them descending. If I ask the query to sort the data by descending date, it will sort them ascending. Weird Wait, it gets better: If I have two records, both from the same year, the issue goes away.
Long story short, after three days, 1 gallon of coffee and a trip to the nearest church, I found the solution:
SELECT ...
LTrim(Convert(varchar(14), inLot.MfgDate, 101)) 'Mfg Date'
...
The difference is a little space between "'Mfg" and "Date'". That sorted my query result accordingly.
I think I know why this happened: Just because the column name in the Order Clause matched the alias of the column name on the Select Statement, SQL thought it should sort them according to the resulting recordset (or cursor, or what ever it is), which is a string (varchar(14) to be exactly) whose first 4 digits belong to the recorded year. As soon as the names were different, it sorted them according to the original value of the column, which is a datetime.
For those who are curious, this was done using Microsoft's SQL 2000, excecuted in the Query Analyzer.
Hope it Helps!!!!
Tarh Ik
PS: This posting has been posted AS IS.