Convert DateTime for MySQL using C#

I want to change the DateTime for MySQL in C#.

My MySQL database only accept this format 1976-04-09 22:10:00.

In C# have a string who have a date value:

string str = "12-Apr-1976 22:10";

I want to convert for MySQL then it look like:

1976-04-12 22:10

How I can change them or how other programmer do this by using dd mm hh yy method? Can anyone tell me about them?

Solution 1:

Keep in mind that you can hard-code ISO format

string formatForMySql = dateValue.ToString("yyyy-MM-dd HH:mm:ss");

or use next:

// just to shorten the code
var isoDateTimeFormat = CultureInfo.InvariantCulture.DateTimeFormat;

// "1976-04-12T22:10:00"
dateValue.ToString(isoDateTimeFormat.SortableDateTimePattern); 

// "1976-04-12 22:10:00Z"    
dateValue.ToString(isoDateTimeFormat.UniversalSortableDateTimePattern)

and so on

Solution 2:

If your string format for the DateTime is fixed you can convert to the System.DateTime using:

string myDate = "12-Apr-1976 22:10";
DateTime dateValue = DateTime.Parse(myDate);

Now, when you need it in your specific format, you can then reverse the process, i.e.:

string formatForMySql = dateValue.ToString("yyyy-MM-dd HH:mm");

edit - updated code. For some strange reason DateTime.ParseExact wasnt playing nice.

Solution 3:

I would strongly suggest you use parameterized queries instead of sending values as strings in the first place.

That way you only need to be able to convert your input format to DateTime or DateTimeOffset, and then you don't need to worry about the database format. This is not only simpler, but avoids SQL injection attacks (e.g. for string values) and is more robust in the face of database settings changes.

For the original conversion to a DateTime, I suggest you use DateTime.ParseExact or DateTime.TryParseExact to explicitly specify the expected format.

Solution 4:

This works for me:

1.Extract date from oracle data base and pass it to variable

 string lDat_otp = "";

  if (rw_mat["dat_otp"].ToString().Length <= 0)
  {
      lDat_otp = "";
  }
  else
  {
      lDat_otp = rw_mat["dat_otp"].ToString();
  }

2.Conversion to mysql format

DateTime dateValue = DateTime.Parse(lDat_otp);
string formatForMySql = dateValue.ToString("yyyy-MM-dd HH:mm");

3.Pass formatForMySql variable to procedure or to something else