SQL Server 2000 Order By Date

[aconybeare]

Hi,

I'm using SQL Server 2000 and am having a bit of trouble sorting by date

I've been getting incorrect results till I started converting the date to

YYYYMMDD - ORDER BY CONVERT(CHAR(8),a.Date_Assigned,112) DESC

The above is great if multiple records aren't inserted a day.

Does anyone know the code to convert a date to YYYYMMDDHHMMSS?

Cheers Al

[szlamany]

If you are using DATETIME datatype you should not have a problem with ORDER BY DATEASSIGNED

I believe I have seen cases where the ORDER by used the select-list column for ordering - maybe that was a situation where a UNION was used to put two SELECT statements together.

From BOL - other CONVERT styles are:

Without century (yy) With century (yyyy)
Standard
Input/Output**
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)

I would really like to see more of the query you are using - I do not believe that CONVERT() is required.

[aconybeare]

Hi,

Think I might have a solution

CONVERT(VARCHAR(23),GETDATE(),126) 

--Outputs YYYY-MON-DDTHH:MM:SS.MMM(24H) FORMAT

Cheers Al

[szlamany]

I would still like to see your whole query...

[aconybeare]

szlamany,

Sorry just missed your post

I'm using datetime datatype, when ordering by date found that 2007 dates were coming out in the wrong order

Here is my query which was getting odd results

SELECT l.Leave_Id, CONVERT(CHAR(11),l.Start_Date,106) As Start_Date, CONVERT(CHAR(11),l.End_Date,106) As End_Date, l.Status, l.Leave_Type, l.Revised_Papers_Chk, l.Comment,
	 CASE WHEN CONVERT(CHAR(8),l.Start_Date,112) <= CONVERT(CHAR(8),GETDATE()+@Lead,112) AND CONVERT(CHAR(8),l.End_Date,112) >= CONVERT(CHAR(8),GETDATE(),112) THEN 'red' -- Currently on Leave
	 WHEN CONVERT(CHAR(8),l.Start_Date,112) > CONVERT(CHAR(8),GETDATE(),112) THEN 'blue' -- Future
	 WHEN CONVERT(CHAR(8),l.End_Date,112) < CONVERT(CHAR(8),GETDATE(),112) THEN 'black' -- History/In the past
	 ELSE 'green' END "Colour_Code" -- UnKnown 
FROM Leave l
WHERE l.User_ID=@User_ID
ORDER BY CONVERT(VARCHAR(23),l.Start_Date,126) Desc

[szlamany]

Just to diagnose this a bit - could you try this?

SELECT l.Leave_Id, CONVERT(CHAR(11),l.Start_Date,106) As Start_Date_Col, CONVERT(CHAR(11),l.End_Date,106) As End_Date, l.Status, l.Leave_Type, l.Revised_Papers_Chk, l.Comment,
	 CASE WHEN CONVERT(CHAR(8),l.Start_Date,112) <= CONVERT(CHAR(8),GETDATE()+@Lead,112) AND CONVERT(CHAR(8),l.End_Date,112) >= CONVERT(CHAR(8),GETDATE(),112) THEN 'red' -- Currently on Leave
	 WHEN CONVERT(CHAR(8),l.Start_Date,112) > CONVERT(CHAR(8),GETDATE(),112) THEN 'blue' -- Future
	 WHEN CONVERT(CHAR(8),l.End_Date,112) < CONVERT(CHAR(8),GETDATE(),112) THEN 'black' -- History/In the past
	 ELSE 'green' END "Colour_Code" -- UnKnown 
FROM Leave l
WHERE l.User_ID=@User_ID
ORDER BY l.Start_Date Desc

[aconybeare]

Your query worked fine, when I changed the column alias back to what I originally had

As Start_Date_Col --> As Start_Date

I got the wrong order again

The thing is I would like to keep the original column name

[szlamany]

From what GOOGLE appears to be telling me you have stumbled upon the fact that the ORDER by is using the column in the SELECT list (this is a requirement in the SQL-92 standard - but not in SQL-99 - maybe it's a compatibility setting on your server).

http://www.thescripts.com/forum/thread79214.html gives a hint of what I believe this is related to.

I've seen this behavior myself - basically you CONVERT or CAST a column in the SELECT list and all of a sudden the ORDER BY starts using that re-constituted data for sorting. I have also done what you are doing - CONVERT or CAST again in the ORDER BY to get around this.

Thanks for taking the time to show that the ORDER BY was in fact using the "select-list" column. Maybe someone will come along with more info for us...

[aconybeare]

No, no it is I who should be thanking you, I managed to to get it working without understanding what the problem was, treating the symptoms and not the cause if you like.

Thanks for your help