problem with sorting date in a grid view

[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 sorted as text, i.e. 1/2/2006 shows up before 10/2/2005. Is there a way that I can explicitly define this column as a date type in ASP.net so that it sorts correctly? Of course in MySQL the column is defined as a date type and manually appending an ORDER BY clause to the select statement functions correctly. I'm developing in VS 2005 if that makes any difference.


Any help is truly appreciated!

 

[adamroof]

this is lengthy, and dont know if you can do the tsql in mySQL...

basically you need to
CONVERT(varchar(10),tblAuditActions.auditDate,120)
to get the best sorting

Stored Proc
   @orderby int
AS
        SELECT * FROM table
	ORDER BY 
		CASE 
			WHEN @orderby = '1' THEN CONVERT(varchar(50),Configurations.PartNumber)
		            	WHEN @orderby = '2' THEN CONVERT(varchar(50),Configurations.configType)
	            		WHEN @orderby = '3' THEN CONVERT(varchar(50),Configurations.RevisionNumber)
	            		WHEN @orderby = '4' THEN CONVERT(varchar(50),Formulations.MoldOption)
	            		WHEN @orderby = '5' THEN CONVERT(varchar(50),Configurations.ODInch)
	            		WHEN @orderby = '6' THEN CONVERT(varchar(50),Configurations.IDInch)
	            		[b]WHEN @orderby = '7' THEN CONVERT(varchar(10),tblAuditActions.auditDate,120)[/b]
	             END 
	DESC

    Sub dgSort(ByVal sender As Object, ByVal e As DataGridSortCommandEventArgs)
        If Session("sortDir") = "DESC" Then
            Session("sortDir") = "ASC"
            Session("orderBy") = e.SortExpression
        Else
            Session("sortDir") = "DESC"
            Session("orderBy") = e.SortExpression
        End If
        BindEngToDo()        
    End Sub

    Function checkOrderBy(ByVal ob As String) As Integer
        Select Case ob
            Case ""
                checkOrderBy = 7
            Case "PartNumber"
                checkOrderBy = 1
            Case "ConfigType"
                checkOrderBy = 2
            Case "RevisionNumber"
                checkOrderBy = 3
            Case "MoldOption"
                checkOrderBy = 4
            Case "ODInch"
                checkOrderBy = 5
            Case "IDInch"
                checkOrderBy = 6
            Case "auditDate"
                checkOrderBy = 7
            Case Else
                Audit("dgParts OrderBy Value Incorrect", "Function Error: Line 315")
        End Select
    End Function

    Sub BindEngToDo()
        Dim sqlCom As SqlCommand = New SqlCommand("sp_EngToDo", sqlCon)
        sqlCom.CommandType = CommandType.StoredProcedure
        sqlCom.Parameters.Add("@orderby", SqlDbType.Int, 4).Value = checkOrderBy(Session("orderBy"))
     'add other parms and execute
     ...

 

[ca8msm]

basically you need to
CONVERT(varchar(10),tblAuditActions.auditDate,120)
to get the best sorting

Why is converting a date into a string (varchar) the best method to sort a date? Surely the best method is to sort it as a date and then format it as needed for the GridView?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[adamroof]

datagrid/gridview has had this issue, some other posts that i found... thread855-1196653 and thread855-707293 - why consume the extra IDB event and resource .net side, when you can just present the data to the grid in the order you want from sql

Sql QA test>
sql orders it like table #1, grid sees it and orders it like table #2 (dont fully know why), so forcing sql side to table #3 has worked out for some.
(same cost per select per my run)

CREATE TABLE #testDates (date1 smalldatetime,orNum int)
INSERT INTO #testDates VALUES ('1/2/2006',5)
INSERT INTO #testDates VALUES ('1/2/2005',10)
INSERT INTO #testDates VALUES ('1/10/2006',4)
INSERT INTO #testDates VALUES ('10/2/2006',2)
INSERT INTO #testDates VALUES ('12/1/2006',1)
INSERT INTO #testDates VALUES ('12/10/2006',1)
INSERT INTO #testDates VALUES ('2/10/2006',3)
INSERT INTO #testDates VALUES ('4/12/2005',9)
INSERT INTO #testDates VALUES ('10/2/2005',8)
INSERT INTO #testDates VALUES ('10/3/2005',7)
INSERT INTO #testDates VALUES ('10/10/2005',6)

SELECT date1,orNum FROM #testDates
ORDER BY date1 DESC

SELECT date1,orNum FROM #testDates
ORDER BY CONVERT(varchar(10),date1,101) DESC

SELECT date1,orNum FROM #testDates
ORDER BY CONVERT(varchar(10),date1,120) DESC

DROP TABLE #testDates

my 2 cents

 

[jwrz200t]

thanx for ur replies. But i have strange problem, the sorting works fine 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????

 

[adamroof]

your list is ordering like table #2 per my examples (for sql users)

To get it like Table #3 change...

SELECT Date FROM Table ORDER BY Date

try

SELECT Date FROM Table ORDER BY DATE_FORMAT(Date,'%Y/%m/%d');


MySQL Refs

http://dev.mysql.com/doc/refman/5.0/en/using-date.html

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html