SQL Server Performance

whats wrong in the Order By Clause

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, Apr 2, 2008.

  1. isa New Member

    Hello everyone, I m suing SQL Server 2005, here is my query, which displays the last 7 days data in this i used the order by clause for sorting my data . My query returns the following dates in this manner:

    LastUpdate Count
    4/1/2005 5
    3/28/2008 4
    3/29/2008 2
    3/31/2008 10

    Here is my Query:

    SELECT Convert(Char(15),LastUpdate,106) as LastUpdate,COUNT(TvsRecordID)as Count

    FROM TvsRecords

    where LastUpdate between DATEADD(dd, -7,getdate())
    and getdate()
    and TvsFormStatusCode =@TvsFormStatusCode
    GROUP BY Convert(Char(15),LastUpdate,106)

    ORDER BY LastUpdate desc

    in the result , it display April data first but other dates of previous month shows properly, kindly tell me whats wrong with this query and y not 4/1/2008 shows after the 3/31/2008 .Kindly reply me and Thanx in Advance.
  2. Luis Martin Moderator

    I'm not a developer, but if you convert lastupdate to char, then the order by use a char to sort instead a date format.
    Could be this the reason?
  3. jagblue New Member

    after looking at result
    it looks good to me
    if you convert date to 106 format it will store date in dd mm yyyy
    '01 04 2008'
    '28 03 2008'
    '29 03 2008'
    and so on
    and 01 comes before 28 ...
    change the formet to something like 111 or 112
  4. FrankKalis Moderator

    This DATEADD(dd, -7,getdate()) is your bug, because you don't remove the time portion from GETDATE() first before filtering the rows. Any rows with a LastUpdate before whatever the current time is, will not pass the filter.

  5. Madhivanan Moderator

    Dont sort by chars. Sort by dates
    Select dateadd(day,datediff(day,0,LastUpdate),0),.........
    order by dateadd(day,datediff(day,0,LastUpdate),0)
  6. nrutter New Member

    Look carefully at your data the first date has a year of 2005 not 2008

Share This Page