CODEWITHSUNDEEP
c#exceldatetimedatatable
Geek
Geek

Reputation: 9

How to convert string with any format To DateTime

I have a DateTime as string with different formats in Excel. I want to convert the string format to DateTime using c#. but I am getting the following exception:

string was not recognized as valid date time.

DateTimeOffset.Parse("09/20/2019 17:25:59");

Expected:

09/20/2019 17:25:59

Actual:

System.FormatException

Upvotes: 0

Views: 156

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186803

First of all, there is no universal format, since date representations are ambiguous, a classical example is

 01/02/03 == 2 Jan 2003 (USA)  
 01/02/03 == 1 Feb 2003 (Russia) 
 01/02/03 == 3 Feb 2001 (China)

If you have a collection of expected formats, you can try ParseExact, e.g.

 string[] possibleFormats = new[] {
   "MM'/'dd'/'yyyy HH:mm:ss",   // USA
   "dd'.'MM'.'yyyy HH:mm:ss",   // Russia
   "yyyy'-'MM'-'dd HH:mm:ss",   // China
   "yyyy'-'MM'-'dd'T'HH:mm:ss", // ISO 8601
 };

 string value = @"09/20/2019 17:25:59";

 DateTimeOffset result = DateTimeOffset.ParseExact(
   value, 
   possibleFormats, 
   CultureInfo.InvariantCulture, 
   DateTimeStyles.AssumeLocal);

Upvotes: 1

darcane
darcane

Reputation: 483

You can use DateTime.ParseExact Doc here

var dateThatDesired = DateTime.ParseExact("09/20/2019 17:25:59","MM/dd/yyyy HH:mm:ss",CultureInfo.InvariantCulture);

If you have limited numbers of formats in the excel table you can test them with DateTime.TryParseExact() and if return with a success you can use the parsed dateTime which you will get with out parameter.

Upvotes: 1

Related Questions