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# i 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? Are anyone tell me about them.

Answer 1

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.

Answer 2

Keep in mind that you can hard-code ISO format

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

or use next:

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

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

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

and so on

Answer 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.