Convert DateTime for MySQL using C#

Question

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?

abatishchev · Accepted Answer · 2013-03-19T18:18:36.980

78

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

edited Mar 19 '13 at 18:18

answered Sep 03 '10 at 05:55

abatishchev

98,240
88
296
433

3

hh:mm is 12hr format, you'll need HH:mm. I just updated my code with that section – Jason Jong Sep 03 '10 at 06:14
Great answer! Small syntactical error: DataTimeFormat should be DateTimeFormat. – Derek W Mar 19 '13 at 13:37
Do not pass dates to MySQL like this. Use https://stackoverflow.com/a/28925999/34092 instead. – mjwills Apr 01 '20 at 03:15
In case the `SortableDateTimePattern` is not found on `CultureInfo.InvariantCulture.DateTimeFormat` try using `DateTimeFormatInfo.InvariantInfo.SortableDateTimePattern` – sisir sagar Jan 06 '21 at 12:16

Jason Jong · Answer 2 · 2010-09-03T06:13:04.213

17

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.

edited Sep 03 '10 at 06:13

answered Sep 03 '10 at 05:44

Jason Jong

4,310
2
25
33

btw, your code doesn't work out of the box, I don't know why. However just `DateTime.Parse` works properly – abatishchev Sep 03 '10 at 05:56

score 6 · Answer 3 · answered Sep 03 '10 at 06:25

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.

With my experience with MySQL, probably because of collation settings, it not always accepts `System.DateTime` and the best way to guarantee proper work on any server is explicitly convert date to string in format `yyyy-MM-dd ..` — abatishchev, Sep 03 '10 at 06:59

score 1 · Answer 4 · edited Feb 14 '15 at 06:58

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

Convert DateTime for MySQL using C#

4 Answers4

Linked

Related