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

Are you sure you're storing these values as strings in your database? Good database design would store them as DateTime (or equivalent) objects. — ProgrammingLlama, Dec 16 '19 at 08:03
Yes, its been stored as string. Not sure why this was decided as this database exists from beginning — 230490, Dec 16 '19 at 08:05
Don't use strings, use dates and set the correct date style instead. Modifying the cell value doesn't change how it's formatted. It can make Excel treat that value as a string instead of a date though, breaking any date-related formulas — Panagiotis Kanavos, Dec 16 '19 at 08:19
PS: You don't to load a DataTable, you can use LoadFromDataReader. Even if you want a DataTable, you don't need SqlDataAdapter to do it — Panagiotis Kanavos, Dec 16 '19 at 08:22

Nguyễn Văn Phong · Accepted Answer · 2019-12-16T08:22:25.427

3

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.

edited Dec 16 '19 at 08:22

answered Dec 16 '19 at 08:08

Nguyễn Văn Phong

13,506
17
39
56

Do i need to do it for all rows explicitly before exporting? – 230490 Dec 16 '19 at 08:13
1

Just to nit-pick, you don't "have to" convert to a DateTime. You could just do it using substrings. – Hans Kilian Dec 16 '19 at 08:17
This won't work. Excel will either *not* recognize the string as a date, or do so and use its default date formatting instead of the string's value – Panagiotis Kanavos Dec 16 '19 at 08:20
Yeah, If I were in his shoes, I could just convert to DateTime only instead of To string – Nguyễn Văn Phong Dec 16 '19 at 08:21
True that. I will convert to datetime instead of string again. @PhongNguyen: in the solution given, im not able to pass my datatable.rows[1][0] as first argument to parseexact – 230490 Dec 16 '19 at 09:06

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

1 Answers1