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

Question

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.

Possible duplicate of [Convert DateTime Value into String in Mysql](http://stackoverflow.com/questions/2392413/convert-datetime-value-into-string-in-mysql) — Linkan, Oct 20 '16 at 12:31
The obvious thing to point out is that `LIKE` works on text/char fields and will not work on Date or DateTime fields. — apokryfos, Oct 20 '16 at 12:38

score 1 · Accepted Answer · edited May 23 '17 at 12:08

1

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

edited May 23 '17 at 12:08

Community

1
1

answered Oct 20 '16 at 12:27

score 0 · Answer 2 · answered Oct 20 '16 at 12:30

0

How about something like > DateTime.Now.Date.AddDays(1-DateTime.Now.Day)?

query = "UPDATE permanent_days SET total_days='"+sum;
query += "' WHERE emp_id_fk='"+_ID+"' AND ";
query += "'date >  '"+DateTime.Now.Date.AddDays(1-DateTime.Now.Day).ToString("yyyy-MM-dd")+"'";

answered Oct 20 '16 at 12:30

Jonny

1,037
7
15

You'd also have make sure that `date` is less than the last day of the current month. – juharr Oct 20 '16 at 12:41
@juharr Yes of course. I made assumptions I shouldn't have made. – Jonny Oct 20 '16 at 13:50

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

2 Answers2