Date parsing from string to DateTime in c#

Question

I am exporting date values("24/11/2016") from excel file to SQL database table in C#.NET. I am using the following code into my function to parse the date values from string to Datetime. But it is not working. I tried to debug it but when it comes on that line, it's terminating. Anybody know what is the problem.

var date = row["Date"].ToString();

DateTime dates;    
string format = "MM-dd-yyyy";

if (!DateTime.TryParseExact(date, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dates))
{ 
  continue;
}                           
else
{
   dates = DateTime.Parse(date); //terminating at this line
}

I am curious why there are 2 parsing commands? If the TryParse fails, why do you think Parse will succeed? — dana, Mar 09 '17 at 05:59
thanks for correcting me. But the actual problem is in parsing string values into Datetime. — PradeepCh, Mar 09 '17 at 06:10

Adil · Answer 1 · 2017-03-09T06:55:23.510

Your format should be dd-MM-yyyy as you have 24/11/2016 as date, you can learn more about string formats in this MSDN article Custom Date and Time Format Strings

Change

string format = "MM-dd-yyyy";

To

string format = "dd-MM-yyyy";

Edit based on comments by OP - Storing formatted date in SQL server

The DateTime is stored in a SQL server in standard format that is not in fact the presentation format we see like "dd-MM-yyy". This article Solving the Datetime Mystery explains the internal SQL server format.

Excerpt from Solving the Datetime Mystery

So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when I didn't supply the date in my first example. SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.

SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes. The code in Figure 3 then converts each set of 4 bytes into an integer.

Appreciation for the upvoters: take a look suggested code and code in the question are same, that should be `string format = "dd-MM-yyyy"`, @Adil: Now it is perfect after edit.. lol — sujith karivelil, Mar 09 '17 at 05:57
thanks for correcting me about that format. But the problem is in parsing string value into Datetime. Why it is not converting into Datetime. I have date values into excel file like "24/11/2016" and i have to store it into sql database table like "2016-11-24". — PradeepCh, Mar 09 '17 at 06:01
@un-lucky, I already suggest dd-MM-yyyy, The statement you point was not suggested as correction although it looks like. Anyway thanks for letting me clear up the ambiguity! — Adil, Mar 09 '17 at 06:02
SQL server storage of date is not irrespective of the presentation format the one you are trying i.e. dd-MM-yyyy, See this post, http://stackoverflow.com/questions/1143259/what-is-the-internal-representation-of-datetime-in-sql-server about the internal format of datetime. You can always format it before you present for instance on GUI. — Adil, Mar 09 '17 at 06:10

score 0 · Answer 2 · answered Mar 09 '17 at 07:34

Finally i did it using the following code:

string format = "dd/MM/yy";

if (!DateTime.TryParseExact(date, format, CultureInfo.InvariantCulture, DateTimeStyles.None,out dates))

{ continue; }

else

{ dates = DateTime.ParseExact(date, format, CultureInfo.InvariantCulture); }

String datetime = dates.ToString("yyyy-MM-dd");

Thanks to all.

Date parsing from string to DateTime in c#

2 Answers2