Parsing any valid DateTime format to SQL Server compatible format?

Question

I am from India and here we follow dd-mm-yyyy date format.

My problem is that, our various client needs their date time to be displayed in the above format. But the SQL Server we use as our backend,does not recognizes dd-mm-yyyy as a valid date time format. What we generally do is to convert the given date to mm-dd-yyyy using CultureInfo either by Using Convert.ToDateTime() or DateTime.Parse or DateTime.TryParse()

Also, I came accross another situation, when my input date is in correct format say MM-dd-yyyy or MM/dd/yyyy or yyyy-MM-dd, but my local system date is other than above, then it throws exception, input string in not in correct format. I am not able to figure out how to resolve it automatically.

Exisitng Custom Method: But this fails most the times in most of the scenarios.

 /// <summary>
    /// Parses string value from a supplied value to DateTime.
    /// Usage: var i = Common.ParseDate(yourDateString);
    /// </summary>
    /// <param name="value"></param>
    /// <returns></returns>
    private static DateTime ParseDate(string value)
    {
        DateTime i;
        if (!DateTime.TryParse(value, out i))
            return Convert.ToDateTime("01/01/1700",CultureInfo.InvariantCulture); 
// Assuming 01/01/1700 as default value instead of null.
        return i;
    }

Please suggest change in the above so that , my method will automatically convert any datetime to SQL Server Compatible format like yyyy-mm-dd.

Usage should be something like this:

Input:
DateTime dt = DateTimeParser("29-12-2013"); // in case or double figure months and dates

Output of dt: either 2013-12-29 or 12/29/2013

DateTime dt = DateTimeParser("9-2-2013"); // in case or single figure months and dates

Output of dt: either 2013-2-9 or 2/9/2013 (sql-server compatible)

Please note: The datetime conversion should be system clock independent.

Thanks in advance

Answer 1

I had this exact thing wanted once, though we were in a position to assume what the user meant since correctness wasn't important (for something like MS Excel does when you input a text date time).

Actually you could collect all the possible date-time formats from the built in cultures, and also many custom formats you could define yourself. I once wanted a very comprehensive date time format matching.

I have done something very similar to nawfal's answer. Here's a solution combining his answer with my large set of formats:

static string[] GetDateTimeFormats()
{
    var defaultFormats = CultureInfo.GetCultures(CultureTypes.AllCultures)
                                    .SelectMany(x => x.DateTimeFormat.GetAllDateTimePatterns())
                                    .Distinct(); //to speed up things

    //discard some formats that're not worthy of consideration
    var goodDefaultFormats = defaultFormats.Where(x => !IsPoorFormat(x) && !IsConflictingFormat(x));

    var customFormats = GetCustomDateTimeFormats();

    var allFormats = goodDefaultFormats.Concat(customFormats).ToArray();

    //a technique to get comma separated time formats, 
    //for eg, from dd-MM-yyyy h:mm:ss tt we get -> dd-MM-yyyy, h:mm:ss tt
    var moreCustomFormats = allFormats.Select(f => new 
                                      { 
                                          f, 
                                          i = f.IndexOf(" h", StringComparison.OrdinalIgnoreCase) 
                                      })
                                      .Where(x => x.i >= 0)
                                      .Select(x => new { x.f, c = x.f[x.i - 1], x.i })
                                      .Where(x => !char.IsPunctuation(x.c) && x.c != 't')
                                      .Select(x => x.f.Insert(x.i, ","));

    allFormats = allFormats.Union(moreCustomFormats).ToArray(); //Union removes duplicates

    return allFormats;
}

static bool IsPoorFormat(string format)
{
    //all discardable formats in case any
    string[] ignorables = { "HH", "MMMM yyyy", "MMMM, yyyy", "yyyy MMMM", "yyyy.M", "yyyy-MM", 
                            "MMMM,yy", "MMMM, yy", "MMMM,yyyy", "MMMM, yyyy", "yyyy. MMMM" };

    return ignorables.Contains(format);
}

//to remove conflicting date formats, for example, 
//formats like MM-dd-yy, yy-MM-dd, dd-MM-yy etc can be conflicting
static bool IsConflictingFormat(string format)
{
    //in this example we discard formats like M-d-yy, yy-MM-dd etc, but keep dd-MM-yy
    //in case you want to keep MM-dd-yy, the placeholders array should be { 'd', 'y' },
    //and similarly if the preferred format is yy-MM-dd, array should be { 'M', 'd' }
    var placeholders = new[] { 'M', 'y' };

    var separators = new[] { ' ', '.', '-', '/' };

    var patterns = placeholders.Select(x => x.ToString())
                               .SelectMany(x => new[] { x, x + x })
                               .SelectMany(x => separators, (x, y) => x + y);

    return patterns.Any(format.StartsWith);
}

static string[] GetCustomDateTimeFormats()
{
    return new[] 
    {
        "dddd, MMMM d, yyyy h:mm:ss tt",
        "dddd, MMMM d, yyyy H:mm:ss",
        "dddd, MMMM d, yyyy h:mm tt",
        "dddd, MMMM d, yyyy H:mm",

        "dddd, MMM d, yyyy h:mm:ss tt",
        "dddd, MMM d, yyyy H:mm:ss",
        "dddd, MMM d, yyyy h:mm tt",
        "dddd, MMM d, yyyy H:mm",

        "ddd, MMMM d, yyyy h:mm:ss tt",
        "ddd, MMMM d, yyyy H:mm:ss",
        "ddd, MMMM d, yyyy h:mm tt",
        "ddd, MMMM d, yyyy H:mm",

        "ddd, MMM d, yyyy h:mm:ss tt",
        "ddd, MMM d, yyyy H:mm:ss",
        "ddd, MMM d, yyyy h:mm tt",
        "ddd, MMM d, yyyy H:mm",



        "dddd, MMMM d yyyy h:mm:ss tt",
        "dddd, MMMM d yyyy H:mm:ss",
        "dddd, MMMM d yyyy h:mm tt",
        "dddd, MMMM d yyyy H:mm",

        "dddd, MMM d yyyy h:mm:ss tt",
        "dddd, MMM d yyyy H:mm:ss",
        "dddd, MMM d yyyy h:mm tt",
        "dddd, MMM d yyyy H:mm",

        "ddd, MMMM d yyyy h:mm:ss tt",
        "ddd, MMMM d yyyy H:mm:ss",
        "ddd, MMMM d yyyy h:mm tt",
        "ddd, MMMM d yyyy H:mm",

        "ddd, MMM d yyyy h:mm:ss tt",
        "ddd, MMM d yyyy H:mm:ss",
        "ddd, MMM d yyyy h:mm tt",
        "ddd, MMM d yyyy H:mm", 

        ///////////////////////////

        "dddd, d MMMM, yyyy h:mm:ss tt", 
        "dddd, d MMMM, yyyy H:mm:ss", 
        "dddd, d MMMM, yyyy h:mm tt", 
        "dddd, d MMMM, yyyy H:mm", 

        "dddd, d MMM, yyyy h:mm:ss tt", 
        "dddd, d MMM, yyyy H:mm:ss", 
        "dddd, d MMM, yyyy h:mm tt", 
        "dddd, d MMM, yyyy H:mm", 

        "ddd, d MMMM, yyyy h:mm:ss tt", 
        "ddd, d MMMM, yyyy H:mm:ss", 
        "ddd, d MMMM, yyyy h:mm tt", 
        "ddd, d MMMM, yyyy H:mm", 

        "ddd, d MMM, yyyy h:mm:ss tt", 
        "ddd, d MMM, yyyy H:mm:ss", 
        "ddd, d MMM, yyyy h:mm tt", 
        "ddd, d MMM, yyyy H:mm", 



        "dddd, d MMMM yyyy h:mm:ss tt", 
        "dddd, d MMMM yyyy H:mm:ss", 
        "dddd, d MMMM yyyy h:mm tt", 
        "dddd, d MMMM yyyy H:mm", 

        "dddd, d MMM yyyy h:mm:ss tt", 
        "dddd, d MMM yyyy H:mm:ss", 
        "dddd, d MMM yyyy h:mm tt", 
        "dddd, d MMM yyyy H:mm", 

        "ddd, d MMMM yyyy h:mm:ss tt", 
        "ddd, d MMMM yyyy H:mm:ss", 
        "ddd, d MMMM yyyy h:mm tt", 
        "ddd, d MMMM yyyy H:mm", 

        "ddd, d MMM yyyy h:mm:ss tt", 
        "ddd, d MMM yyyy H:mm:ss", 
        "ddd, d MMM yyyy h:mm tt", 
        "ddd, d MMM yyyy H:mm", 

        /////////////////////////////////

        "yyyy, MMMM d h:mm:ss tt", 
        "yyyy, MMMM d H:mm:ss", 
        "yyyy, MMMM d h:mm tt", 
        "yyyy, MMMM d H:mm", 

        "yyyy, MMM d h:mm:ss tt", 
        "yyyy, MMM d H:mm:ss", 
        "yyyy, MMM d h:mm tt", 
        "yyyy, MMM d H:mm", 

        "yyyy, MM d h:mm:ss tt", 
        "yyyy, MM d H:mm:ss", 
        "yyyy, MM d h:mm tt", 
        "yyyy, MM d H:mm", 



        "yyyy MMMM d h:mm:ss tt", 
        "yyyy MMMM d H:mm:ss", 
        "yyyy MMMM d h:mm tt", 
        "yyyy MMMM d H:mm", 

        "yyyy MMM d h:mm:ss tt", 
        "yyyy MMM d H:mm:ss", 
        "yyyy MMM d h:mm tt", 
        "yyyy MMM d H:mm", 

        "yyyy MM d h:mm:ss tt", 
        "yyyy MM d H:mm:ss", 
        "yyyy MM d h:mm tt", 
        "yyyy MM d H:mm", 

        ///////////////////////

        "yyyy, d MMMM h:mm:ss tt", 
        "yyyy, d MMMM H:mm:ss", 
        "yyyy, d MMMM h:mm tt", 
        "yyyy, d MMMM H:mm", 

        "yyyy, d MMM h:mm:ss tt", 
        "yyyy, d MMM H:mm:ss", 
        "yyyy, d MMM h:mm tt", 
        "yyyy, d MMM H:mm", 

        "yyyy, d MM h:mm:ss tt", 
        "yyyy, d MM H:mm:ss", 
        "yyyy, d MM h:mm tt", 
        "yyyy, d MM H:mm", 



        "yyyy d MMMM h:mm:ss tt", 
        "yyyy d MMMM H:mm:ss", 
        "yyyy d MMMM h:mm tt", 
        "yyyy d MMMM H:mm", 

        "yyyy d MMM h:mm:ss tt", 
        "yyyy d MMM H:mm:ss", 
        "yyyy d MMM h:mm tt", 
        "yyyy d MMM H:mm", 

        "yyyy d MM h:mm:ss tt", 
        "yyyy d MM H:mm:ss", 
        "yyyy d MM h:mm tt", 
        "yyyy d MM H:mm",

        ////////////////////////////////

        "MMMM d, yyyy h:mm:ss tt",
        "MMMM d, yyyy H:mm:ss",
        "MMMM d, yyyy h:mm tt",
        "MMMM d, yyyy H:mm",

        "MMM d, yyyy h:mm:ss tt",
        "MMM d, yyyy H:mm:ss",
        "MMM d, yyyy h:mm tt",
        "MMM d, yyyy H:mm",



        "MMMM d yyyy h:mm:ss tt",
        "MMMM d yyyy H:mm:ss",
        "MMMM d yyyy h:mm tt",
        "MMMM d yyyy H:mm",

        "MMM d yyyy h:mm:ss tt",
        "MMM d yyyy H:mm:ss",
        "MMM d yyyy h:mm tt",
        "MMM d yyyy H:mm",

        ////////////////////////////////////

        "d MMMM, yyyy h:mm:ss tt", 
        "d MMMM, yyyy H:mm:ss", 
        "d MMMM, yyyy h:mm tt",
        "d MMMM, yyyy H:mm",  

        "d MMM, yyyy h:mm:ss tt", 
        "d MMM, yyyy H:mm:ss", 
        "d MMM, yyyy h:mm tt",
        "d MMM, yyyy H:mm",  



        "d MMMM yyyy h:mm:ss tt", 
        "d MMMM yyyy H:mm:ss", 
        "d MMMM yyyy h:mm tt",
        "d MMMM yyyy H:mm",  

        "d MMM yyyy h:mm:ss tt", 
        "d MMM yyyy H:mm:ss", 
        "d MMM yyyy h:mm tt",
        "d MMM yyyy H:mm",  

        /////////////////////////

        "dddd, MMMM d, yyyy",
        "dddd, MMM d, yyyy",

        "ddd, MMMM d, yyyy",
        "ddd, MMM d, yyyy",



        "dddd, MMMM d yyyy", 
        "dddd, MMM d yyyy",

        "ddd, MMMM d yyyy",
        "ddd, MMM d yyyy",

        //////////////////////////

        "dddd, d MMMM, yyyy", 
        "dddd, d MMM, yyyy", 

        "ddd, d MMMM, yyyy", 
        "ddd, d MMM, yyyy",  



        "dddd, d MMMM yyyy",
        "dddd, d MMM yyyy",

        "ddd, d MMMM yyyy", 
        "ddd, d MMM yyyy", 

        ///////////////////////////

        "MMMM d, yyyy",
        "MMM d, yyyy",

        "MMMM d yyyy",
        "MMM d yyyy",

        //////////////////////////

        "d MMMM, yyyy", 
        "d MMM, yyyy",

        "d MMMM yyyy", 
        "d MMM yyyy",

        //////////////////////////

        "yyyy, MMMM d", 
        "yyyy, MMM d", 

        "yyyy MMMM d", 
        "yyyy MMM d", 

        //////////////////////////

        "yyyy d MMMM", 
        "yyyy d MMM", 

        "yyyy, d MMMM", 
        "yyyy, d MMM", 

        ///////////////////////////

        "d MMMM",
        "d MMM",

        /////////////////////////////

        "MMMM d", 
        "MMM d",

        ////////////////////////////

        "dd",
    };
}

Answer 2

As Habib says, to save it in database you do not need to know any format, just save the DateTime object and your ado.net connector knows to handle it.

Your problem should be at the point of parsing the user's input string to .NET DateTime object. Now without knowing the culture specific settings of the user, you're doomed. You can't have a universal way of handling it. Consider the date 12/12/12, it could mean dd/MM/yy or MM/dd/yy or any other combination.

---

Your best option is to get the information about the user, his culture and parse accordingly. Or demand him to input in a format you specifically specify.

---

Well, you could rely on some heuristics to match against common formats, by assuming the format but its completely brittle. This can be handy only if the accuracy is not an issue.

private static DateTime ParseOrDefault(string value)
{
    DateTime result;
    var sett = DateTimeStyles.AllowWhiteSpaces; //and whatever that is
    var formats = GetDateTimeFormats //whatever cultures that you want to consider.
    (
        new CultureInfo("en-GB"), //prioritize your order
        new CultureInfo("en-IN"), 
        CultureInfo.CurrentCulture, 
        CultureInfo.InvariantCulture
    );

    if (!DateTime.TryParseExact(value, formats, CultureInfo.InvariantCulture, sett, out result))
        return Convert.ToDateTime("01/01/1700", CultureInfo.InvariantCulture);

    return result;
}

string[] GetDateTimeFormats(params CultureInfo[] cultures)
{
    return cultures.SelectMany(x => x.DateTimeFormat.GetAllDateTimePatterns())
                   .Distinct()
                   .ToArray();
}

DateTime.ParseExact and DateTime.TryParseExact have overloads to achieve matching against multiple formats. The above approach assumes the input string from user is in en-GB, en-IN etc cultures first, ie, in case of 12/12/12 it treats it as dd/MM/yy. That's some assumption, but it's as close you can get without knowing the input and your only option is to assume. Cautioned.

Call it like:

Common.ParseOrDefault(yourDateString);

---

Another option could be that you match against all possible formats you know excluding the conflicting formats. For example, if you want to consider only dd/MM/yy formats you can filter out MM/dd/yy and yy/MM/dd formats. Now you get a large number of formats and avoid conflicts. Can be slightly better.

static string[] formats; //made static for performance reasons

private static DateTime ParseOrDefault(string value)
{
    formats = formats ?? GetDateTimeFormats();

    DateTime result;
    var sett = DateTimeStyles.AllowWhiteSpaces; //and whatever that is

    if (!DateTime.TryParseExact(value, formats, CultureInfo.InvariantCulture, sett, out result))
        return Convert.ToDateTime("01/01/1700", CultureInfo.InvariantCulture);

    return result;
}

static string[] GetDateTimeFormats()
{
    var allFormats = CultureInfo.GetCultures(CultureTypes.AllCultures)
                                .SelectMany(x => x.DateTimeFormat.GetAllDateTimePatterns())
                                .Distinct(); //to speed up things

    //discard some formats that're not worthy of consideration
    var goodFormats = allFormats.Where(x => !IsConflictingFormat(x));

    return goodFormats.ToArray();
}

//to remove conflicting date formats, for example, 
//formats like MM-dd-yy, yy-MM-dd, dd-MM-yy etc can be conflicting
static bool IsConflictingFormat(string format)
{
    //in this example we discard formats like M-d-yy, yy-MM-dd etc, but keep dd-MM-yy
    //in case you want to keep MM-dd-yy, the placeholders array should be { 'd', 'y' },
    //and similarly if the preferred format is yy-MM-dd, array should be { 'M', 'd' }
    var placeholders = new[] { 'M', 'y' };

    var separators = new[] { ' ', '.', '-', '/' };

    var patterns = placeholders.Select(x => x.ToString())
                               .SelectMany(x => new[] { x, x + x })
                               .SelectMany(x => separators, (x, y) => x + y);

    return patterns.Any(format.StartsWith);
}

Answer 3

Try This Code If your input date is "dd/mm/yyyy" then it convert Sql complient format else it gives you message date format is not expected format.

 var dd = string.Empty;
    var mm = string.Empty;
    var yy = string.Empty;
    string actualDate = "18/07/2012";
    string finalDate = string.Empty;
    if (actualDate.ToString().Contains('/'))
    {
        dd = string.Format("{0:00}", Convert.ToInt32(actualDate.ToString().Split('/')[0]));
        mm = string.Format("{0:00}", Convert.ToInt32(actualDate.ToString().Split('/')[1]));
        yy = string.Format("{0:0000}", Convert.ToInt32(actualDate.ToString().Split('/')[2]));
        finalDate = dd + "/" + mm + "/" + yy;
    }

    string dateString = finalDate; // <-- Valid
    string format = "dd/MM/yyyy";
    DateTime dateTime;
    if (!DateTime.TryParseExact(dateString, format, CultureInfo.InvariantCulture,
        DateTimeStyles.None, out dateTime))
    {
       lblDate.Text= "Excel Date is not correct format.it should be in dd/mm/yyyy";
    }
    else
    {
        actualDate =
            DateTime.ParseExact(dateString, format,
                                   CultureInfo.InvariantCulture,
                                   DateTimeStyles.None).ToString();
    }

    Label1.Text = actualDate;

Answer 4

Personnally I use parameters. Otherwise you can force a particular DateTime format in SQL Server.

Answer 5

You don't need to parse the date to a format. Use DateTime datatype in your SQL Server for the field, which is irespective of the format. Also make sure you use SqlParameter when interacting with your SQL Server through C#. DateTime format should only be used for displaying dates. Like:

using(SqlConnection conn = new SqlConnection("connectionstring"))
using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "Insert into yourtable(datecolumn) VALUES (@pDate);";
    cmd.Parameters.AddWithValue("@pDate", DateTime.Now);
    cmd.Connection = conn;
    //.....
}