The excel file I am reading has the datetime column value as "25-OCT-13 01.08.24.732000000 PM" and to read it into .Net's DateTime format I tried
DateTime.TryParseExact(certDate, "dd-MMM-yy hh.mm.ss.fffffffff tt", CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out certifiedDateTime)
but it always is false. What am i missing.
I am using C# in Visual Studio 2013, .Net 4.5
Appears you can only have up to 7 f elements.
This works:
string certDate = "25-Oct-13 01.08.24.7320000 PM";
DateTime certifiedDateTime;
DateTime.TryParseExact(certDate, "dd-MMM-yy hh.mm.ss.fffffff tt",
CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None,
out certifiedDateTime);
certifiedDateTime.Dump();
It seems this is because DateTime is stored as a 64-bit number representing ticks. There's a good relevant article from Eric Lippert called Precision and accuracy of DateTime.
To get that additional precision you may have to look at a different library. Perhaps Jon Skeet's Noda Time, though that may not help either.
However, I'm guessing you don't actually want sub-second precision to 9 decimal places (particularly as it's unlikely you have that in the raw data unless the trailing zeroes are a big coincidence).
As such, if you're happy sacrificing this excess precision you could use Regex or similar to parse out the milliseconds and reduce them to 7 digits.
As such, if you're happy sacrificing the excess precision, MarcinJuraszek's comment to use a format including 0 sounds like the best approach:
DateTime.TryParseExact(certDate, "dd-MMM-yy hh.mm.ss.fffffff00 tt",
CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None,
out certifiedDateTime);