Have Geek Brain

How to sort a DataGrid column

I have a datagrid that contains data that is retrieved from a database, and the datagrid displays the data in the same format as in the database.

One of the columns is DateFrom, which is the column I would like to sort. The date stored in the database as Varchar is therefore sorted alphabetically, e.g. 2/2004, 2/2008, 4/2003. I want to convert DatFrom to DateTime type and sort the values ​​numerically before displaying in datagrid.

Is there a way to do this?

thanks

ASEI

0


a source to share


5 answers


I created a small demo table (test). We entered several test dates and ran a select query:



Create Table test(dt varchar(10))
Go
Insert Into test(dt)
Select '2/2004'
Union Select '2/2008'
Union Select '4/2003'
Go

Select *
From test
Order By Convert(datetime, '1/' + dt, 103)
+1


a source


Probably wrap the column to implement the IComparable interface so you can provide a custom view, here's an example .



0


a source


I think I understand what you are asking here. Let me know if I leave the base. I think the easiest way to fix this is in a select statement to get your data from the database, but this is not necessarily the best method. This is what I came up with:

Since your "DateFrom" is in the "MM / yyyy" format, you cannot perform a direct CAST () operation. The best way to get the actual DATETIME value I can think of is to parse the current DateFrom column and treat all dates as the first month, then you can set the format "MM / yyyy" on the datagrid column and it will still display right. The CAST () operator could be something like this:

SET DATEFORMAT MDY

SELECT CAST(SUBSTRING(DateFrom, 0, CHARINDEX('/',DateFrom))
        + '/1/'
        + SUBSTRING(DateFrom, CHARINDEX('/',DateFrom) +1, 4) AS DATETIME) AS DateFrom

I know this is not the most elegant method, but it should work as long as your date format is consistent. Good luck!

0


a source


I'm not sure if I understood your question. That said, how about sorting the strings on the SQL side with something like:

SELECT ...
ORDER BY Substring(DateFrom, CHARINDEX('/', DateFrom) + 1, 4) 
         + Lpad(DateFrom, 7, '0');

It will change the original 2/1999 to 199902/1999 so that it can be compared as a string. The part after the slash is only because I didn't want to truncate the string, since I don't need it.

or

SELECT ...
ORDER BY Cast(int, 
              Substring(DateFrom, CHARINDEX('/', DateFrom) + 1, 4) 
       * 100 
       + Cast(int, 
              Substring(DateFrom, 1, CHARINDEX('/', DateFrom));
0


a source


Thanks guys. I'll see. I have to give up this project for a while. I'll let you know if I can get it to work or not.

0


a source






More articles:


All Articles