Question Order by date

[tqmd1]

Dear Experts

Table1 has following data

date----------weight
14/01/2010------5
14/01/2010------10
15/01/2010------20
02/02/2010------35

I use followin query

str = "SELECT RANK() OVER(ORDER BY date) AS sno ,date, SUM(weight) AS weight FROM table1  GROUP BY Date"
        dt = GetTable(str)

The query generates these results

1----02/02/2010------35
2----14/01/2010------15
3----15/01/2010------20

This is not order by date as you can see
I want to display data index on date order as

1----14/01/2010------15
2----15/01/2010------20
3----02/02/2010------35

Please help

 

[Tom]

Select 
    CONVERT(CHAR(10), tbl.Date, 110) As FormattedDate,
    Sum(Weight) As Weight
From Table1 As tbl
Group By Convert(Char(10), tbl.Date, 110)

This should format your dates so that it appear as MM-DD-YYYY.

 

[tqmd1]

Finally I have following codes

 str = "Select  CONVERT(CHAR(10), Date, 110) As Date,  Sum(Weight) As Weight From gpass As tbl Group By Convert(Char(10), tbl.Date, 110) order by Convert(Char(10), tbl.Date, 110)"
  dt = GetTable(str)

it does not display any error message but it does not sort date field,

What is wrong?

 

[cjard]

How about going back and re-designing your table so that the DATE column is a DATE datatype instead of a string?

Just because a string can store something that looks like a date (to us humans) doesnt mean you should use it that way