How can I use the LIKE operator to search for a date in a month?

Question

I have the following database structure and I want to select everything from a specific user and month. However it does return the specific user data but all the months.
The month i selected in this example is 2014-02 or February 2014

date       |user      | some more data |
----------------------------------------
2014-01-20 |user1     | more data      | //will not be skipped
2014-02-01 |user1     | more data      |
2014-02-01 |user2     | more data      | //will be skipped
2014-02-02 |user1     | more data      |
2014-05-02 |user1     | more data      | //will not be skipped

I call the following query via a prepared statement function. The LIKE operator seems like it isn't working.

$query = "SELECT * FROM table1 WHERE user = ? AND date LIKE ?"
$user = "user1";     //These 2 actually come from user input so:
$date = "2014-02%";  //$user = $username; $date = $y."-".$m."%";
//function that runs the prepared statement (simplified)
//this function works for all other queries.
$result = $database->runQuery($query, array($user, $date));
while($row = mysqli_fetch_array($result){
    echo $row['date'];
    echo $row['user'];
    echo $row['some more data'];
    echo "<br>";
}

This prints

2014-01-20 user1 more data //this line should not be printed but it does.
2014-02-01 user1 more data
2014-02-02 user1 more data
2014-05-02 user1 more data //also should not be printed.

So my question is what am I doing wrong with the LIKE operator?

My actual query.

//The system holds phone call data.
//dcontext is a variable that decides in which column the user is found.
//in case of dcontext = outgoing calls
//clid = username + extensions
//src = users phone number
//dst = to phone number (this can be extension from another user in the system)
//which wont be registered double in this case.

//when dcontext = incomming calls
//clid and src = the caller ID/phone number from the person calling.
//dst  = the users phone number.

//when dcontext = internal call transfer
//clid = username + extension
//src = users phone number
//dst = transfered number

"SELECT * FROM table WHERE 
(dcontext = ? AND (clid = ? OR src = ? OR dst = ? OR dst = ?)) OR 
(dcontext = ? AND dst = ?) OR 
(dcontext = ? AND (clid = ? OR src = ?)) AND 
date LIKE ?" 

date type = DateTime

score 1 · Answer 1 · answered Jun 02 '14 at 08:42

1

Don't use like. Use WHERE user = ? and year(date) = ? and month(date) = ?, assuming date is a Date or DateType type, and not a varchar.

answered Jun 02 '14 at 08:42

nl-x

11,762
7
33
61

Date is indeed DateType, but this did still not give the right results I now added some extra information to the question with the full query. with the answer from below already added. – kpp Jun 02 '14 at 08:54

Bartek · Accepted Answer · 2014-06-02T08:58:56.467

0

I think you should CAST your date into string format, so try with:

SELECT * FROM table1 WHERE user = ? AND CAST(date as char) LIKE ?

Or use DATE_FORMAT function

SELECT * FROM table1 WHERE user = ? AND DATE_FORMAT(date, '%Y-%m-%d') LIKE ?

EDIT:

Another way to filter date parts is to extract their parts:

SELECT * FROM table1 WHERE user = ? AND EXTRACT(year FROM date) = ? AND EXTRACT(month from date) = ?

But you have to pass year and month as a separate arguments

EDIT2:

I put some of the elements of your query in braces to make sure your AND operator work correctly

Try with this query:

SELECT * FROM table WHERE 
((dcontext = ? AND (clid = ? OR src = ? OR dst = ? OR dst = ?)) OR 
(dcontext = ? AND dst = ?) OR 
(? AND (clid = ? OR src = ?))) AND (EXTRACT(year FROM date) = ? AND 
(EXTRACT(month FROM date) = ? )

edited Jun 02 '14 at 08:58

answered Jun 02 '14 at 08:22

Bartek

1,349
7
13

`Didn't work` means it has returned more results than you expected? – Bartek Jun 02 '14 at 08:32
yeah the result were the same, it still gave me all results from the specified user, I checked the database the `type of date = datetime`, set up in `y-m-d h:m:s` so `0000-00-00 00:00:00` so I changed my query to this `DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s') LIKE ?` – kpp Jun 02 '14 at 08:34
I also want to say, that all methods I described work in my case. The funny thing is, that your query also works :D Maybe your query is more complex and you use `or` operator? – Bartek Jun 02 '14 at 08:39
my complete query is indeed a bit more complicated but every bit of the query works except for the sorting on date, whenever I use OR in it I throw some () ill post it in my question. Since even the extract didnt work. – kpp Jun 02 '14 at 08:44
I combined both answers yours and nl-x and the query seems to work now. so thanks. – kpp Jun 02 '14 at 09:11

How can I use the LIKE operator to search for a date in a month?

2 Answers2