CODEWITHSUNDEEP
asp.netdatetime
user2116780
user2116780

Reputation:

datetime parsing from excel sheet in asp.net

i want to get a datetime value from excel sheet and take the highest and the lowest date

i read the excel sheet and put it in datatable : i tried this code : 

protected void CheckTheFP(DataTable data)
    {
        if (data.Rows.Count != 0)
        {
            DateTime ds = new DateTime();

            err.Text = DateTime.TryParseExact(data.Rows[0][2].ToString(), "MM/dd/yy hh:mm tt",
               CultureInfo.InvariantCulture,
               System.Globalization.DateTimeStyles.None,
               out ds) + "" ;

        }
    }

but i always get false ... don't know why ?

and is there a way to sort this datatable or take the highest and lowest date

enter image description here

this the excel sheet i read from

Upvotes: 2

Views: 412

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460208

This format string should work: "M/dd/yy h:mm tt". I've used single M because the month has one digit, the same applies to the hours. I've used CultureInfo.InvariantCulture to prevent that all / will be replaced with your actual date-separator (in case that it's different).

You can use LINQ:

var allDateTimes = data.AsEnumerable()
    .Select(row => DateTime.ParseExact(row.Field<string>("Time"), "M/dd/yy h:mm tt", CultureInfo.InvariantCulture));
DateTime min = allDateTimes.Min();
DateTime max = allDateTimes.Max();

If you want to be on the safe side you should use TryParseExact, for example with this code:

IEnumerable<DateTime> allDateTimes = data.AsEnumerable()
  .Select(row => {
     string time = row.Field<string>("Time").Trim();
     DateTime dt;
     if (DateTime.TryParseExact(time, "M/dd/yy h:mm tt", CultureInfo.InvariantCulture, DateTimeStyles.None, out dt))
        return (DateTime?) dt;
     return null;  // set a breakpoint here to see which value could not be parsed
  })
  .Where(dt => dt.HasValue)
  .Select(dt => dt.Value);
DateTime min = allDateTimes.Min();
DateTime max = allDateTimes.Max();

Edit: you: "when i try to use it on the date 11/2/14 4:42 PM you see the 11 is not in M datetime format

The month is not the problem. Use single d instead because the days can have a single digit also.

So: "M/d/yy h:mm tt"

Upvotes: 2

Related Questions