Convert the string with datetime format-"yyyyMMddhhmmssfff" to "DD-MM-YYYY HH:MM) before exporting DataTable to excel

Question

I am exporting the result from a database to excel using ExcelPackage(EPPLUS nuget package). The datetime field from the database is in string and in the format - yyyyMMddhhmmssfff which is not readable in excel after exporting-ex- 20191211034422455. So i need to convert this to readable format like DD-MM-YYYY HH:MM or any other readable format. How do i achieve it. Do i need to parse each row and convert explicitly? And for conversion, do i have to first convert it to datetime and then to string again?

My current code Snippet

//Method which reads data from database 
using (SqlConnection sqlcon = new SqlConnection(ConnStrng))
{
    sqlcon.Open();
    string query = "Select Vehicleid,Speed,Datetime from VehiclDBk where Speed>30 and Datetime between '" + startDate + "' and '" + endDate + "'";
    SqlDataAdapter sqlda = new SqlDataAdapter(query, sqlcon);
    valRes = new DataTable();
    sqlda.Fill(valRes);
    DBView.DataSource = valRes;
    sqlcon.Close();
}

// Method which exports data when user presses export button
using (ExcelPackage pck = new ExcelPackage())
{
     ExcelWorksheet ws = pck.Workbook.Worksheets.Add(DateTime.Now.ToShortDateString());
     ws.Cells["A1"].LoadFromDataTable(valRes, true);
     pck.SaveAs(new FileInfo(savefile.FileName));
}

Any help would be greatly appreciated. Also is there a possibility that may be i can just convert it to DateTime and export as DateTime instead of string to datetime and again to string before exporting

Answer 1

You Should convert it to DateTime and then to string again.

In case you got a DateTime having milliseconds, use the following formatting (Link live demo here)

string format = "yyyyMMddHHmmssfff";
string dateTime = "20191211034422455";
var x = DateTime.ParseExact(dateTime, format, CultureInfo.InvariantCulture);
var result = x.ToString("dd-MM-yyyy HH:MM"); // In my opinion, You just convert to Date to use instead of then to string like this.