Convert unusual string to DateTime w/out foreach

Question

Update My Solution:

var rowsToAdd = (from t in dtEntry.AsEnumerable().Cast<DataRow>()
                 let startDate = (
                 t.Field<string>("StartDate").Length > 0)
                     ? DateTime.Parse(t.Field<string>("StartDate").Split(new Char [] {'('})[0], CultureInfo.InvariantCulture)
                     : DateTime.Today.AddMonths(-3)
                 where startDate > filterDate
                 select t);

Original Question: I get a DateTime string from an external API that looks like this:

10/14/2014 8:30 AM (America/Los Angeles)

I have all the data in a datatable called dtEntry which I'm using below.

None of the built-in c# DateTime conversion functions seem to work. They all result in format exeptions. Does anyone know how I could do this? The other catch is that I'm using LINQ (see below).

DataRow[] rows = (from t in dtEntry.AsEnumerable().Cast<DataRow>()
                       let startDate = (
                       t.Field<string>("StartDate").Length > 0)
                           ? DateTime.Parse(t.Field<string>("StartDate"))
                           : DateTime.Today.AddMonths(-3)
                       where startDate > filterDate
                       select t).ToArray();

Any ideas? I've got the ternary operator in there because I need to handle empty strings as well.

there's nothing built-in to .NET to parse that. You'll either have to pre-parse that and store a UTC date, or split the string in two. The first half the date/time (e.g. `"10/14/2014 8:30 AM"`), you can parse normally. The second, you'll have to parse into an offset by which you can then make the result of the first parse into a UTC. — Peter Ritchie, Oct 06 '14 at 19:00
Also to stop the exceptions you can use TryParse instead of Parse. It will at least let you deal with the error in a nicer way. http://msdn.microsoft.com/en-us/library/system.datetime.tryparse(v=vs.110).aspx — tdbeckett, Oct 06 '14 at 19:01
I don't get how you parse "10/14/2014 8:30 AM (America/Los Angeles)" *with foreach*... Would you mind to show sample? — Alexei Levenkov, Oct 06 '14 at 19:05
@AlexeiLevenkov what I meant by parsing it _with foreach_ was looping over my data table row by row and then parsing the dates that way. I went away from that approach to using linq because it seemed cleaner. — funkhouserben, Oct 06 '14 at 19:21
@user3444160 I started to use TryParse but I couldn't figure out how to use it within a LINQ query... — funkhouserben, Oct 06 '14 at 19:21
@PeterRitchie thanks. I started wondering about this just after I posted the question. My solution splits the string apart into a date that can be parsed normally. Appreciate it. — funkhouserben, Oct 06 '14 at 19:22
`ParseExact` as CriketetOnSO posted is likely the easiest to get at the first part of the string. — Peter Ritchie, Oct 06 '14 at 19:28
@OregonAppDev I know you already have your solution, but here is an example of how to use a TryParse in a linq statement; DateTime xo; var x1 = x.Where(xi => DateTime.TryParse(xi, out xo)); — tdbeckett, Oct 07 '14 at 19:05
@OregonAppDev this too will work: DateTime xo; var x1 = (from xi in x where DateTime.TryParse(xi, out xo) select xi); — tdbeckett, Oct 07 '14 at 19:07

score 4 · Accepted Answer · edited May 23 '17 at 10:33

4

You can split your string based on space and then Take(3) elements from the result array, Join them back using string.Join and then use DateTime.ParseExact or DateTime.TryParseExact like:

string str = "10/14/2014 8:30 AM (America/Los Angeles)";
string newStr = string.Join(" ", str.Split().Take(3));

DateTime parsingDateTime;
if (!DateTime.TryParseExact(newStr, "M/d/yyyy h:mm tt", CultureInfo.InvariantCulture, DateTimeStyles.None,
    out parsingDateTime))
{
    //invalid datetime
}

EDIT: You have to ignore (America/Los Angeles) part of string, otherwise there is no way for parsing using such string. You can find TimeZone for Region and then create DateTime for that parameter. See this: Get timezone by Country and Region

edited May 23 '17 at 10:33

Community

1
1

answered Oct 06 '14 at 18:59

CriketerOnSO

2,600
2
15
24

2

Clearly that extra appendage is meant to communicate time zone information. You are suggesting ignoring it. That seems wrong. – DonBoitnott Oct 06 '14 at 19:08
1

@DonBoitnott, there is no way to parse date in such string then. I have edited my answer to suggest OP to find TimeZone as well. – CriketerOnSO Oct 06 '14 at 19:11
@CriketerOnSO even though I was looking for a solution using LINQ (per my code) I did end up using essentially this solution: parsing out the timezone string and using CultureInfo to find it instead, so I'm accepting this as the answer. Thanks for your help. – funkhouserben Oct 06 '14 at 19:19
@DonBoitnott is correct, the time zone information should be taken into account. See my answer for parsing the time zone info. – Metro Smurf Oct 06 '14 at 19:30

score 2 · Answer 2 · edited May 23 '17 at 11:55

The accepted answer does not take into account the time zone part. My assumption here is the time zone is a standard time zone identifier which can be translated from the Unicode.org site. And based off this other SO Answer (.NET TimeZoneInfo from Olson time zone) which provides a helper method from the Unicode.org site, you can then parse the api time to your time:

string apiTime = "10/14/2014 8:30 AM (America/Los Angeles)";

int timeZoneStart = apiTime.IndexOf('(');

string timeZonePart = apiTime.Substring(timeZoneStart)
    .Replace("(", string.Empty) // remove parenthesis
    .Replace(")", string.Empty) // remove parenthesis
    .Trim() // clear any other whitespace
    .Replace(" ", "_"); // standard tz uses underscores for spaces
    // (America/Los Angeles) will become America/Los_Angeles

string datePart = apiTime.Substring(0, timeZoneStart).Trim();

DateTime apiDate = DateTime.Parse(datePart);

TimeZoneInfo tzi = OlsonTimeZoneToTimeZoneInfo(timeZonePart);

DateTime apiDateTimeConverted = TimeZoneInfo.ConvertTime(apiDate, tzi);

The method above, OlsonTimeZoneToTimeZoneInfo, is from the SO answer linked above.

thanks for this. I don't think I was understanding CultureInfo.InvariantCulture - I looked it up and now I see what you're saying. I'm not sure if I'll end up adding this - but it would probably be a good idea. In my case I know my code will always be running in the same time zone - and I'm really only needing the day part of the datetime. But this is helpful. — funkhouserben, Oct 06 '14 at 20:17
@OregonAppDev - even if you only need the day part, the day in your time zone may be different than where the api sent the date time from. Just something to be aware of. — Metro Smurf, Oct 06 '14 at 22:06

Convert unusual string to DateTime w/out foreach

2 Answers2