CODEWITHSUNDEEP
c#asp.netexceldatetimegridview
Amit Lal
Amit Lal

Reputation: 11

Convert time entered from excel file to gridview in asp.net

I have an excel file where I am adding time in the format- "10:14:16" and added code 

dt = ExcelLibrary.DataSetHelper.CreateDataTable(FilePath, "abc");

to convert and display this excel file in a gridview in asp.net. It shows the value as "0.426574074074074".

I am unable to save this data in the sql server database as I need the format to be same as mentioned in excel file. Please help me.

Upvotes: 1

Views: 587

Answers (2)

Adil
Adil

Reputation: 148150

You need to use DateTime.FromOADate(double date), it returns a DateTime equivalent to the specified OLE Automation Date.

DateTime dt = DateTime.FromOADate(double.Parse(stringVariableContainingDateTime));

The d parameter is a double-precision floating-point number that represents a date as the number of days before or after the base date, midnight, 30 December 1899. The sign and integral part of d encode the date as a positive or negative day displacement from 30 December 1899, and the absolute value of the fractional part of d encodes the time of day as a fraction of a day displacement from midnight. d must be a value between negative 657435.0 through positive 2958465.99999999, MSDN.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460208

It is a OA-Date. OA Date means a date stored on a computer in OLE Automation format. You can use double.Parse to get a double from the string and DateTime.FromOADate to parse it to DateTime:

string date = "0.426574074074074";
double num;
if (double.TryParse(date, NumberStyles.Float, CultureInfo.InvariantCulture, out num))
{
    DateTime dt = DateTime.FromOADate(num);
    // 12/30/1899 10:14:16
    // use DateTime.TimeOfday to get the TimeSpan:
    TimeSpan ts = dt.TimeOfDay; // 10:14:16
}

Upvotes: 1

Related Questions