Sorting DataGrid Column as Date

Question

I have a program I'm writing to help my company keep track of tool calibration. I have all of the tools saved in a SQLite database, which does not have the option to set a column type to DATETIME. So I've stored the dates in a M/D/YYYY format to keep it simple.

I have the model pull the tool inventory from the database and return the populated table to the viewmodel.

From here I've bound the viewmodel data table to the data grid, also binding each of the data grid columns to the appropriate columns in the data table.

I want the user to be able to sort the "calibration due" column from newest to oldest or oldest to newest.

The problem is that since both SQLite and the DataGrid control don't seem to have options for DateTime columns, the datagrid continues to sort these as strings.

The DataGrid columns are set as DataGridTextColumns since I couldn't find out if a templated column would fix this, or even how to use one.

I.E. :

9/26/2017

9/12/2017

8/5/2017

8/28/2017

I've tried converting the dates to a MM/DD/YYYY format, but that didn't work. Could anyone help me figure out what I need to do to get the proper sorting for these dates?

I'm using Caliburn.Micro and SQLite if that helps narrow down the possible solutions.

CheckOutInModel:

    public DataTable RetrieveToolRoster()
    {
        string db_command = "SELECT [id], [cal_date] FROM inventory WHERE [cal_date] IS NOT NULL ORDER BY [id] ASC;";
        SQLiteConnection db_connection = new SQLiteConnection(Properties.Settings.Default.db_connectionstring);
        SQLiteDataAdapter db_dataAdapter = new SQLiteDataAdapter(db_command, db_connection);
        DataTable tr_dataTable = new DataTable();

        try
        {
            db_connection.Open();
            db_dataAdapter.Fill(tr_dataTable);
            db_connection.Close();
            return tr_dataTable;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error:\r\n" + ex.Message);
            return null;
        }
    }

CheckOutInViewModel:

    private DataTable _toolRoster;
    public DataTable ToolRoster
    {
        get { return _toolRoster; }
        set
        {
            _toolRoster = value;
            NotifyOfPropertyChange(() => ToolRoster);
        }
    }
    public void PopulateToolRoster()
    {
        CheckOutInModel coim = new CheckOutInModel();
        ToolRoster = coim.RetrieveToolRoster();
    }

CheckOutInView:

    <DataGrid Grid.Column="0"
              ItemsSource="{Binding ToolRoster}"
              Style="{DynamicResource DataGridStandard}">
        <DataGrid.Columns>
            <DataGridTextColumn Header="Tool ID"
                                Width="*"
                                Binding="{Binding id}"/>
            <DataGridTextColumn Header="Calibration Due"
                                Width="*"
                                Binding="{Binding cal_due, StringFormat={}{0:d}}"/>
        </DataGrid.Columns>
    </DataGrid>

Thank you!

THE SOLUTION

I transferred the data from the data table I filled to a list and returned the list.

CheckOutInViewModel:

    private List<RosterData> _toolRoster;

    public List<RosterData> ToolRoster
    {
        get { return _toolRoster; }
        set
        {
            _toolRoster = value;
            NotifyOfPropertyChange(() => ToolRoster);
        }
    }

CheckOutInModel:

    public List<RosterData> RetrieveToolRoster()
    {
        string db_command = "SELECT [id], [cal_date] FROM inventory WHERE [cal_date] IS NOT NULL ORDER BY [id] ASC;";
        SQLiteConnection db_connection = new SQLiteConnection(Properties.Settings.Default.db_connectionstring);
        SQLiteDataAdapter db_dataAdapter = new SQLiteDataAdapter(db_command, db_connection);
        DataTable tr_dataTable = new DataTable();

        try
        {
            db_connection.Open();
            db_dataAdapter.Fill(tr_dataTable);
            db_connection.Close();
            List<RosterData> rd = new List<RosterData>();                
            foreach (DataRow dr in tr_dataTable.Rows)
            {
                RosterData rds = new RosterData();
                rds.id = dr[0].ToString();
                rds.cal_date = Convert.ToDateTime(dr[1]);
                rd.Add(rds);
            }
            return rd;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error:\r\n" + ex.Message);
            return null;
        }
    }

RosterData.cs:

public class RosterData
{
    public string id { get; set; }
    public DateTime cal_date { get; set; }
}

Answer 1

Just define your custom class instead of loading a datatable. Use an SQLiteDateReader and convert each record to an element of a List of your custom class

public class RosterData
{
    public int id {get;set;}
    public DateTime cal_date {get;set;}
}

public List<RosterData> RetrieveToolRoster()
{
    string List<RosterData> result = new List<RosterData>();
    string db_command = "SELECT [id], [cal_date] FROM inventory WHERE [cal_date] IS NOT NULL ORDER BY [id] ASC;";
    using(SQLiteConnection db_connection = new SQLiteConnection(Properties.Settings.Default.db_connectionstring))
    using(SQLiteCommand cmd = new SQLiteCommand(db_command, db_connection))
    {
        try
        {
            db_connection.Open();
            using(SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while(reader.Read())
                {
                    RosterData rd = new RosterData()
                    {
                        rd.id = Convert.ToInt32(rd["id"]);
                        rd.cal_date = Convert.ToDateTime(rd["cal_date"]);
                    };
                    result.Add(rd);
               }
           }
           return result;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error:\r\n" + ex.Message);
            return null;
        }

    }
}

.......

private List<RosterData> _toolRoster;
public List<RosterData> ToolRoster
{
    get { return _toolRoster; }
    set
    {
        _toolRoster = value;
        NotifyOfPropertyChange(() => ToolRoster);
    }
}

Answer 2

It may be easier to store/retrieve it as a number. You can then sort it when you query it and simply convert back at display time.

DateTime.Ticks will give you a long which is the number of nanoseconds.

You can then store that in the DB and turn it back into a DateTime on retrieval with:

new DateTime(number)

Sorting on query is then easy, as whichever has the highest number of "ticks" is the most future DateTime.