Hyder Khan Khosa Hyder Khan Khosa - 1 month ago 5
C# Question

'LIKE%' is not working in update query while comparing datetime in database

What i am doing is comparing current date with the 'date' field (which has certain date) in a table 'permanent_days' in MySql database (PhpMyadmin). Data type of that 'date' field is datetime. My source code is as follows:

double sum = previous_total + 0.5;
query = "UPDATE permanent_days SET total_days='"+sum+"' WHERE emp_id_fk='"+_ID+"' AND date LIKE '"+DateTime.Now.ToString("yyyy-MM")+"%'";
command = new MySqlCommand(query, connection, transaction);
command.ExecuteNonQuery();


After i execute this code, i am getting following exception:



Error: Incorrect datetime value: '2016-10% for column 'date' at row 1



Note that this comparison works fine while using 'Select' sql command. However, in 'Update' sql command (as mentioned above), this comparisons throws above mentioned exception. What can be the possible solution.

Answer

You need to first convert your date time to string, then you can use LIKE.

You can use Convert() and Cast(). But better:

WHERE  DATE_FORMAT(dateColumn, '%Y-%m-%d') LIKE '2016-10-%'

See this answer