c# - Parsing any valid DateTime format to SQL Server compatible format? -
i india , here follow dd-mm-yyyy date format.
my problem that, our various client needs date time displayed in above format. sql server use our backend,does not recognizes dd-mm-yyyy valid date time format. convert given date mm-dd-yyyy using cultureinfo either using convert.todatetime() or datetime.parse or datetime.tryparse()
also, came accross situation, when input date in correct format mm-dd-yyyy or mm/dd/yyyy or yyyy-mm-dd, local system date other above, it throws exception, input string in not in correct format. not able figure out how resolve automatically.
exisitng custom method: fails times in of scenarios.
/// <summary> /// parses string value supplied value datetime. /// usage: var = 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 default value instead of null. return i; } please suggest change in above , method automatically convert datetime sql server compatible format yyyy-mm-dd.
usage should this:
input: datetime dt = datetimeparser("29-12-2013"); // in case or double figure months , dates output of dt: either 2013-12-29 or 12/29/2013 datetime dt = datetimeparser("9-2-2013"); // in case or single figure months , dates output of dt: either 2013-2-9 or 2/9/2013 (sql-server compatible) please note: datetime conversion should system clock independent.
thanks in advance
i had exact thing wanted once, though in position assume user meant since correctness wasn't important (for ms excel when input text date time).
actually collect possible date-time formats built in cultures, , many custom formats define yourself. once wanted comprehensive date time format matching.
i have done similar nawfal's answer. here's solution combining answer large set of formats:
static string[] getdatetimeformats() { var defaultformats = cultureinfo.getcultures(culturetypes.allcultures) .selectmany(x => x.datetimeformat.getalldatetimepatterns()) .distinct(); //to speed things //discard 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 comma separated time formats, //for eg, dd-mm-yyyy h:mm:ss tt -> dd-mm-yyyy, h:mm:ss tt var morecustomformats = allformats.select(f => new { f, = 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 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, example, //formats mm-dd-yy, yy-mm-dd, dd-mm-yy etc can conflicting static bool isconflictingformat(string format) { //in example discard formats m-d-yy, yy-mm-dd etc, keep dd-mm-yy //in case want keep mm-dd-yy, placeholders array should { 'd', 'y' }, //and if preferred format yy-mm-dd, array should { '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", }; } 
