SQL Server Performance

The sorting is not working

Discussion in 'SQL Server 2008 General Developer Questions' started by alisag, Mar 18, 2010.

  1. alisag New Member

    Hi all, i have the following code
    SELECT CASE WHEN [Service Header].[Document Type] = 0 THEN 'Quote' ELSE 'ORDER' END AS [Doc Type], [Service Header].[Responsibility Center],
    [Service Header].No_,
    CASE WHEN [Service Header].Status = 0 THEN 'Pending' WHEN [Service Header].Status = 1 THEN 'In Process' WHEN [Service Header].Status = 2 THEN
    'Finished' WHEN [Service Header].Status = 3 THEN 'On Hold' ELSE 'Waiting Parts' END AS [Contract Status], CONVERT(VARCHAR(12),
    [Service Header].[Order Date], 103) AS OrderDate, [Service Header].Name, [Service Header].Description, [Service Header].[Resource Code],
    [Service Header].[Customer No_], [Service Header].[Contract No_],
    CASE WHEN [Service Header].[Response Date] <= '01/01/1900' THEN '' ELSE CONVERT(VARCHAR(12), [Service Header].[Response Date], 103)
    END AS ResponseDate, Resource.Name AS [Resource Name], CASE WHEN ([Service Header].[Job Type] = 0)
    THEN 'BILLABLE' WHEN [Service Header].[Job Type] = 1 THEN 'CONTRACT' ELSE 'WARRANTY' END AS JobType
    FROM dbo.[CompanyName$Service Header] AS [Service Header] LEFT OUTER JOIN
    dbo.[CompanyName$Resource] AS Resource ON [Service Header].[Resource Code] = Resource.No_
    WHERE ([Service Header].No_ LIKE 'FS%')
    ORDER BY Resource.Name, CONVERT(VARCHAR(12), [Service Header].[Order Date], 103) DESC
    The sorting for resource name is working fine but it is not working for order Date. year 2009 is coming before 2010 for the same resource name.
    Am i missing something in this.
  2. alisag New Member

    Sorry for this post. I think without doing research on my code i just put in the forum.
  3. FrankKalis Moderator

    No worries! [:)]
  4. Madhivanan Moderator

    It is becuase you are sorting the dates as VARCHARs by converting them
    Thats why you should do formation at the front end application
  5. alisag New Member

    Yes. So i just displayed in VArchar but sorted using Datetime.
  6. Adriaan New Member

    Just look at your ORDER BY clause -
    ORDER BY Resource.Name, CONVERT(VARCHAR(12), [Service Header].[Order Date], 103) DESC
    You are most definitely sorting by the string returned by the CONVERT function.
    To order by a datetime column, just refer to the column - no bells, no whistles ...
    ORDER BY Resource.Name, [Service Header].[Order Date] DESC

Share This Page