strange problem with grid view sorting 1

[jwrz200t]

I am using a grid view which has a column containing dates in ASP.net 2.0. The data is being pulled from a SQL database by which the datasource is connected through OLEDB. The problem that I am running into is that when I click on the date header to sort the field in the gridview, the dates are being sorted in a correct way untill 09/23/2005 but after that the sorting has become erratic that is
09/19/2006
09/16/2006
09/16/2006
09/16/2005
09/16/2005
09/16/2005
09/14/2006
09/14/2006
09/14/2006
09/14/2005
09/13/2006
09/13/2006
09/12/2006
09/12/2006

Can anyone get me out of this????

 

[jbenson001]

is the date column you are referring to a datetime datatype in the database or is it a character column?

 

[jwrz200t]

its a varchar data type

 

[jbenson001]

That's your problem then, either change the datatype of the column to date time, or convert it to a datetime datatype when you query the table.

 

[jwrz200t]

can u please give me the sql command to covert to datetime while querying???

 

[jbenson001]

select 
 case 
    when isdate(datecol) = 1 then convert(datetime, datecol)
 else
     convert(datetime, '01/01/1900')
end  As MyDate
 from yourtable

Jim

 

[adamroof]

Which thread of yours was first?

thread855-1281175

or this one?

is it a MySQL Database (your other post) or MS SQL Database?

 

[jwrz200t]

this is my second post and iam using MS sql database