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

[kvprajapati]

>This is not order by date as you can see.
Field date must be of DateTime or date type.

[tqmd1]

It means there is no solution of this problem in vb.net?

[TomW]

I have already answered this with an example on the other board you posted the same question

[tqmd1]

I have already answered this with an example on the other board you posted the same question

yes, while adopting your codes i modify my codes as

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?

[apegram]

That code isn't converting the field to a datetime, but to a char(10). Replace char(10) with DateTime and see what you get.

Your date format is going to cause a problem when doing the default conversion. However, by consulting this link you can see the proper numeric code to use is 103.

Select Rank() Over (Order By Convert(DateTime, date, 103)), Convert(DateTime, Date, 103) as Date, Sum(Weight) 
From Table1
Group By Convert(DateTime, Date, 103)

That will convert a string in the format 31/1/2010 to a date equal to Jan 31 2010.

[tqmd1]

Sir, please help more

how to Replace char(10) with DateTime

[apegram]

Sir, please help more

how to Replace char(10) with DateTime

Check my prior post again. I had edited additional information and code into it.