Retrieve list of possible DateTime formats from string value

I would like to be able to parse date and/or date-time values from a csv file and get their DateTime format (or in Excel terms NumberFormat).

For example I would like to pass "2008-06-07 00:00:00.000" to a function and have it return something like "yyyy-MM-dd hh:mm:ss.000".

The catch is that there can be many possible date formats in the csv files and that they are not known ahead of time so I can't use DateTime.TryParseExact() since it requires you to know the DateTime format string ahead of time to test to see if it works with a specific value.

By knowing the date format, I could set it to be the custom number format in Excel and put in the value and it will appear exactly as it was in the text of the csv file while also having the ability to use it in Excel formulas.

Of course there could be could be ambiguity so ideally it would be nice to get a list possible date formats and then check against multiple dates to remove some of the options by looking at the intersections of the lists of possible date formats.

I have just discovered the NodaTime API and it looks like it may be able to do something like this, but I haven't seen example code that will achieve this as most of the questions regarding similar queries were asked for earlier versions where it didn't seem to be supported.

Any other methods of doing this would be greatly appreciated as well.

Edit

I think the only way to do this, as Jon Skeet said, is to have a list of the common patterns and test against them. I can get the set of all cultured date-time patterns by doing the following. However this list will be missing some custom patterns that may be common in use. For example (I don't know if this is commonly used or not) yyyy/dd/MM is not in the list.

    private static HashSet<string> _patterns; 
    public static HashSet<string> AllCulturedDateTimePatterns
    {
        get
        {
            if (_patterns != null)
                return _patterns;

            _patterns = new HashSet<string>();
            var cultures = CultureInfo.GetCultures(CultureTypes.AllCultures);
            foreach (var culture in cultures)
            {
                _patterns.UnionWith(culture.DateTimeFormat.GetAllDateTimePatterns());
            }
            return _patterns;
        }
    }
Jon Skeet
people
quotationmark

There's no such thing as "all possible date formats". A format of "'Year:' yyyy 'Month:' MM 'Day:' dd" would be valid, but highly unusual, for example. There's nothing that Noda Time supplies here that would be particularly helpful. I suspect you'll need to create your own list of all the date/time formats you've ever seen - then you can either create a pattern for each format within Noda Time, and try to parse it checking for success in the result, or use DateTime.TryParseExact in the BCL. For example:

BCL version

var allFormats = new List<string>
{
    "yyyy-MM-dd HH:mm:ss",
    "dd/MM/yyyy HH:mm:ss",
    "MM/dd/yyyy HH:mm:ss",
    // etc
};

DateTime ignored;
var matchingFormats = allFormats
    .Where(format => DateTime.TryParseExact(text, format, CultureInfo.InvariantCulture,
                                            DateTimeStyles.None, out ignored))
    .ToList();

Noda Time version

var allFormats = ...; // As before
var allPatterns = allFormats
    .Select(format => LocalDateTimePattern.CreateWithInvariantCulture(format))
    .ToList();

var matchingPatterns = allPatterns.Where(pattern => pattern.Parse(text).Success)
                                  .ToList();

Note that IIRC, we don't currently expose the underlying format string from a LocalDateTimePattern (or any other pattern). We could do so, but we don't at the moment...

people

See more on this question at Stackoverflow