1

I'm inserting a DateTime type into my MySql table as type date. When trying to compare the date with the same DateTime type I get an exception: MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Date > '10/7/2017''.

I've tried applying the ToString() method to the DateTime and still get the same exception. From everything I've read it seems like the comparison should be straightforward.

My SQL statement is as follows:

var last30Days = DateTime.Today.AddDays(-30);

var sql = @"
SELECT * FROM table
WHERE Date >= @last30Days;";
Jessie Cryer
  • 159
  • 2
  • 9
  • This is probably the same issue you are encountering:https://stackoverflow.com/questions/3633262/convert-datetime-for-mysql-using-c-sharp MySql uses a different date format then .net, most likely, so you'll need to convert back and forth to avoid syntax errors. – user1934587390 Nov 06 '17 at 19:42
  • I'm not saying this is incorrect but in my table I can view the date and it's formatted exactly the same. MM/DD/YYYY. I'll check out the post in the meantime. – Jessie Cryer Nov 06 '17 at 19:44
  • How are you using that variable? The error you show doesnt *quite* match that string unless there is a copy paste error. There is not a need to worry about date formats using parameters and the NET DB provider – Ňɏssa Pøngjǣrdenlarp Nov 06 '17 at 20:30

2 Answers2

0

Try this:

string last30Days = DateTime.Now.AddDays(-30).ToString("yyyy-MM-dd HH:mm:ss.fff");

var sql = @"
SELECT * FROM table
WHERE Date >= @last30Days;";

A quick google search found this.

Sam W
  • 599
  • 2
  • 16
0

It may well be worth you looking into the MySQL Connector for .NET which will allow you to run parameterised queries. The library includes types for handling MySQL datetime formats.

Bijan Rafraf
  • 393
  • 1
  • 7