C# Datagridview w/oledb connection date column issues

Question

I've been researching this issue for over a day, read a lot of posts and tried many formatting options but no joy. Essentially I'm populating a datagridview from a database with an OleDbDataAdapter connection. The date columns are formatted properly in the database, but dates come across as very large double numbers (10 digits). I've tried creating a new formatted column in the SQL statement, but just get a shorter number (5 digits), i.e. select START_DATE, datepart(START_DATE) as SDATE format=date9. from table ; So instead of 1776772799, I get 20550 ... when I look at the actual table the formatted value is 06APR2016:00:00:00 ... or 06APR2016 with the date9 database format. The odd thing with my situation is that every attempt to 'format' just puts the format value as a string. I can't embed an image, but the formatted date column show [dd/MMM/yyyy] (or whatever format I try to use) and the unformatted column shows a number, i.e. [1776772799]. Looking for some ideas. thanks

private void loadForm()
    {
        OleDbConnection conn3 = new OleDbConnection(conn3str);
        string qEvent = "select * from tablename ;";
        //string qEvent = select * , datepart(START_DATE) as SDate format=date9. from tablename ;
        OleDbDataAdapter daEvent = new OleDbDataAdapter(qEvent, conn3);
        DataSet dsEvent = new DataSet();

        conn3.Open();
        daEvent.Fill(dsEvent, "event_table");
        conn3.Close();
        dgvMngEvnts.DataSource = dsEvent;
        dgvMngEvnts.DataMember = "event_table";
        dgvMngEvnts.Rows[0].Selected = false;

        //string dgvFormat = "dd/MMM/yyyy";
        //dgvMngEvnts.Columns[4].DefaultCellStyle.Format = dgvFormat;
        dgvMngEvnts.Columns[4].DefaultCellStyle.Format =@"dd/MMM/yyyy";
        //dgvMngEvnts.Columns[4].DefaultCellStyle = new DataGridViewCellStyle { Format = "dd'/'MM'/'yyyy" };
    }

Answer 1

I've solved my issue and wanted to post the answer. I used some of the suggestions here Dave Swersky answer so a big thanks to Dave. The only real difference is for some reason my dates were based on 1960 instead of 1970. I've included the code below. Essentially I followed this process: 1- Create OLEDB connection, get all data into a data table. 2- Create a FixDate method based on Dave's code 3- Add the additional columns, then iterate through the table calling FixDate. 4- Remove unwanted columns, reorder remaining columns. thanks @

private void loadForm()
{
    public static DateTime FixDate(double timestamp)
        {
            DateTime origin = new DateTime(1960, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
            return origin.AddSeconds(timestamp);
        }
    string query = "select * from eventTable ; ";
    OleDbConnection conn3 = new OleDbConnection(conn3str);
    OleDbDataAdapter daEvent = new OleDbDataAdapter(query, conn3);

    conn3.Open();
    DataSet dsEvent = new DataSet();
    DataTable evTable = new DataTable();
    conn3.Close();

    daEvent.FillSchema(evTable, SchemaType.Source);
    daEvent.Fill(evTable);
    evTable.Columns.Add("StartDate", typeof(DateTime));
    evTable.Columns.Add("EndDate", typeof(DateTime));
    evTable.Columns.Add("LastUpDate", typeof(DateTime));
    EnumerableRowCollection<DataRow> evRow = evTable.AsEnumerable();
    for (int i=0; i <= evTable.Rows.Count -1; i++)
    {
            double dbDt1 = Convert.ToDouble(evTable.Rows[i]["START_DATE"]);
            double dbDt2 = Convert.ToDouble(evTable.Rows[i]["END_DATE"]);
            double dbDt3 = Convert.ToDouble(evTable.Rows[i]["LAST_UPDATE"]);
            evTable.Rows[i]["StartDate"] = FixDate(dbDt1);
            evTable.Rows[i]["EndDate"] = FixDate(dbDt2);
            evTable.Rows[i]["LastUpDate"] = FixDate(dbDt3);
            DateTime dbDtDt1 = Convert.ToDateTime(evTable.Rows[i]["StartDate"]);
            DateTime dbDtDt2 = Convert.ToDateTime(evTable.Rows[i]["EndDate"]);
            DateTime dbDtDt3 = Convert.ToDateTime(evTable.Rows[i]["LastUpDate"]);
    }
    evTable.Columns.Remove("START_DATE");
    evTable.Columns.Remove("END_DATE");
    evTable.Columns.Remove("LAST_UPDATE");
    evTable.Columns["StartDate"].SetOrdinal(3);
    evTable.Columns["EndDate"].SetOrdinal(4);
    DataGridView1.DataSource = evTable;
}