Sort

Question

Sort

Rudi Liebenberg

SSC Enthusiast

Points: 100
More actions
June 22, 2004 at 6:22 am

#161736

I have a stored proc as shown below:
SELECT
  [File_ID],
  Created_Date,
  CONVERT(CHAR, Effective_Date, 103) AS Effective_Date,
  Store_Code,
  File_Description,
  LTRIM([File_Name]) AS [File_Name],
  File_Size,
  File_Status
FROM Download_Files
WHERE NOT File_ID IS NULL
This proc is used in reporting. The problem is that when I remove the "AS Effective_Date" part my sort works perfectly on the datetime field, whereas it does not sort correctly with the "AS Effective_Date" in place. Why is this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luigi Visintin SSC Eights! Points: 822 More actions · Answer 1

Hi Rudy,

1. you do not mention if the sort is performed in Sql (ORDER BY Effective_Date) or in your application (VB, ASP or else)

2. sometime Sql gets confused between the column name (if NOT qualified) and the alias, try:

SELECT

Dwn.[File_ID],

Dwn.Created_Date,

CONVERT(CHAR, Dwn.Effective_Date, 103) AS Effective_Date,

Dwn.Store_Code,

Dwn.File_Description,

LTRIM(Dwn.[File_Name]) AS [File_Name],

Dwn.File_Size,

Dwn.File_Status

FROM Download_Files AS Dwn

WHERE NOT Dwn.File_ID IS NULL

ORDER BY Dwn.Effective_Date

HTH

Gigi

AKM SSCommitted Points: 1538 More actions · Answer 2

Depending on the client that is receiving the queery results (VB, Crystal Reports, whatever), there will be different ways for handling the aliased computed (' ... AS ...') column.

Your client program may also be getting confused by the fact that your alias is the same as a base column name. I would try changing

CONVERT(CHAR, Effective_Date, 103) AS Effective_Date,

to

CONVERT(CHAR, Effective_Date, 103) AS Text_Effective_Date,

then seeing how your client gets on with sorting by Text_Effective_Date.

vadba SSChampion Points: 11133 More actions · Answer 3

Another thing to check is that, since the date is returned as character data of the form dd.mm.yyyy (using style 103), it won't sort correctly unless converted back to a date type in your client. If the client expects character data, try style 102 or 111 instead of style 103.

The best way is to return the datetime from SQL Server and format/convert as desired on your client.

Rudi Liebenberg SSC Enthusiast Points: 100 More actions · Answer 4

Thanks to all for replying. I was away for a couple of days, and was only able to look at the feedback today. The reply from mkeast did the trick. Thanks again!