C#: Sorting on date field in a Winforms app

In an sqlite database I have a date field stored as a TEXT property with the following format:

dd/mm/yyyy hh:mm:ss

(e.g., the text in the field looks like: 28/04/2009 19:15:17)

I have bound a DataGridView to the database via the GUI in VS2008. So basically all the code was automatically generated for me. When I start my app it reads the database and displays all the rows in the DataGridView. I can sort on all columns except the date column. I suspect this is because the DataGridView thinks the date column is just plain text. How do I get this to work as I want it to?

UPDATE

As far as I am aware there is no DATE type in sqlite so I hav开发者_JAVA百科e to store as TEXT. Happy to be told otherwise though.

UPDATE The user would like to see the date in dd/mm/yyyy format.

Thank you.

---

If you are treating the date as a string and you want it to sort correctly as a string you should change the date format to yyyy/mm/dd hh:mm:ss, that would make the date string sortable. Ideally you should be treating the data type as a date of course.

---

I would do one of these (in order of preference):

1. Change the database so that it stores the date values as a Date type, not a string. This is "the right thing to do", since you will be able to apply sorting on all levels without any problems. It is likely to be more space efficient as well, since date types are typically just a few bytes in size.
2. If you can't change the database design, change the format in which you store the date text into ISO-8601 format (yyyy-MM-dd HH:mm:ss).
3. Create a layer in between the database and the grid where you read the data into a custom object, where the date value is represented by a DateTime field. Use DateTime.TryParseExact to parse the string from the database into your custom object.

---

There are a number of implementations of SortableBindingList that you can find on the internet, including one from MSDN. This blog example may help. You can use known datatypes and and get correct sorting without having to worry so much about date format strings. The only requirement would be to convert the date string from the db to a datetime in your bound objecttype.