DateTime.Now to mysql datetime

Question

Hi, i got problem with a query, got something like this

command.CommandText = "SELECT " +
        "COUNT(a.`id`) " +
   "FROM " +
        "`messageaccess` a " +
   "WHERE " +
        "a.`Users_LOGIN` = '" + Settings.UserLogin + "' " +
        "AND a.`Status` = '" + Enums.MessageStatus.New + "' " +
        "AND a.`FOLDER` = '" + Enums.MessageFolder.INBOX + "'" +
        "AND a.`ShowAlert` = '" + Enums.YesNo.No + "'" +
        "AND a.`Postponed` <= " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "";

but sql throws me exception 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 '14:40:37' at line 1

tried diffrent combinantions but nothing works :(

Answer 1

You forgot the quotation marks around the date/time thing.

Answer 2

Have a look at named parameterized queries. They take care of these formatting issues for you.

Answer 3

The simple answer is not to embed values directly into the SQL to start with.

Use a parameterized SQL statement, specify the parameter value as DateTime.Now, and all will be well:

• Your SQL will be easier to read (as it'll just be the code, not the data)
• You won't need to worry about formatting of things like numbers and dates
• You won't be vulnerable to SQL injection attacks

Answer 4

You shouldn't build your query appending strings. This is not very safe (sql injection) and you're not taking advantage of the ADO .NET capabilities to set the correct format according the parameter type.

You should use parametrized queries.

Answer 5

try using this line instead:

"AND a.`Postponed` <= NOW()" 

and it should work with the native MySql function for the current time.