Covert C# Datetime to MySql DateTime format

Question

I have been trying to figure out how to add current datetime from c# into mysql column and that attribute/column is also in 'datetime' format. I am writing this code.

   if (comboBox1.Text == "Cash On Delivery")
        {
           MessageBox.Show("Your order has been placed.","",MessageBoxButtons.OK);

           string timeString = DateTime.Now.ToString("YYYY-MM-DD HH:MM:SS");

           string constring = "datasource=localhost;port=3306;username=root;password=root";
           string query = "insert into artgallery.payment_method where payment_method ='" +comboBox1.Text+ "' AND payment_date='" +formatForMySql+ "' AND payment_amount = '" +variables.total_amount+ "' ";

         //rest of the code

        }

I get this some syntax error related to the timeString Im trying to insert in my 'payment_date' column.

possible duplicate of [Convert DateTime for MySQL using C#](http://stackoverflow.com/questions/3633262/convert-datetime-for-mysql-using-c-sharp) — Julien Roncaglia, Jan 24 '15 at 18:57
By the way, there is no `YYYY`, `DD` and `SS` as a [custom date and time specifiers](http://msdn.microsoft.com/en-us/library/8kb3ddd4%28v=vs.110%29.aspx). Custom specifiers are case sensitive. And `mm` is for minutes but `MM` is for months. — Soner Gönül, Jan 24 '15 at 18:58

score 4 · Answer 1 · answered Jan 24 '15 at 18:56

4

The right answer is to stop building your SQL like that to start with. Use parameterized SQL, then specify the DateTime value as the parameter value - you don't need a string representation at all in your code.

It's not immediately clear which driver you're using (there are two for MySQL, IIRC) but you should look at the Parameters property of whichever command type you're using.

It's really important to use parameterized SQL - not just to avoid conversion issues like this, but also to prevent SQL Injection Attacks. It also makes your code considerably simpler to read, in my view.

answered Jan 24 '15 at 18:56

Jon Skeet

1,421,763
867
9,128
9,194

I have not studied parameterized SQL. Can you tell me whats the possible error with this conversion ? – Bolshoi Booze Jan 24 '15 at 19:04
@MirzaTaimoorBaig: Well you've already discovered that you need to make sure you perform the right conversion - and do so *everywhere* in your code, carefully. Why continue with this approach which is insecure for string parameters, rather than find out how to use the accepted best practice for providing values in SQL queries? – Jon Skeet Jan 24 '15 at 19:06
i totally agree to this answer for prevent injection attack, since the questioner already put his raw data from user to sql statement. – Sruit A.Suk Jan 24 '15 at 19:09
@ToonSuperLove: If all the values are `DateTime` or numeric values it's *unlikely* to make a SQL Injection Attack (although it's [possible](http://codeblog.jonskeet.uk/2014/08/08/the-bobbytables-culture/) - but it's worth just getting into the habit of using parmaeterized SQL everywhere, IMO. – Jon Skeet Jan 24 '15 at 19:15

score 0 · Answer 2 · answered Jan 24 '15 at 19:03

put it like this

INSERT INTO table_name( date_column) VALUES (STR_TO_DATE('2014-12-31 00:00:00','%Y-%m-%d %H:%i:%S')

Also, you put M (which mean of month ) in both month, and minute... please recheck your format again here

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Covert C# Datetime to MySql DateTime format

2 Answers2