Error matching dates in PDO

Question

I am trying to update records on a database table where the date column matches the present year and month. I keep getting this error

Incorrect datetime value: '%2018-06%' for column 'created_at'

This is what I am doing

$current_month = date("Y-m");
$points = 4;
$freelancer_id =4;
$update = "UPDATE monthly_limits SET points = :points WHERE freelancer_id = :freelancer_id AND 
created_at LIKE concat('%', :current_month, '%')";
$query = $this->db->prepare($update);
$query->bindParam("points", $points);
$query->bindParam("current_month", $current_month);
$query->bindParam("freelancer_id", $freelancer_id);
$query->execute();

Please how do I resolve this?

You might be better off with `YEAR(created_at) = :year AND MONTH(created_at) = :month`, and pass in `date('Y')` as year and `date('m')` as month. — aynber, Jun 26 '18 at 16:53
No idea, but using a wildcard on the left won't work with indexes, anyway. — aynber, Jun 26 '18 at 16:57
What mysql version are you using? I've never seen that error with a date/datetime column in a `where`. I could set that if you were trying to set the date/datetime column. — user3783243, Jun 26 '18 at 17:09

score 3 · Accepted Answer · answered Jun 26 '18 at 16:56

This is not how dates work in a SQL database. A like statement is meant to be used with string types, whereas dates are a glorified number. Given the style of parameters, I'm betting your best bet is to do two difference checks. One on month and one on year.

You haven't specified your RDBMS, so I'll give a MySQL answer.

UPDATE 
  monthly_limits SET points = :points 
WHERE 
  freelancer_id         = :freelancer_id 
  AND Year(created_at)  = :current_year
  AND Month(created_at) = :current_month

score 1 · Answer 2 · answered Jun 26 '18 at 16:57

1

You cannot use wildcards with dates and the db value as a date.

You would be much better off getting the date pieces you want to compare and use those directly:

$current_year = date("Y"); 
$current_month = date("mm");
$points = 4;
$freelancer_id =4;
//Use the SQL version you have to get the date parts this is general.
$update = "UPDATE monthly_limits SET points = :points WHERE freelancer_id = :freelancer_id AND 
        MONTH(created_at) = :current_month AND YEAR(created_at) = :current_year";
        $query = $this->db->prepare($update);
        $query->bindParam("points", $points);
        //Separated these
        $query->bindParam("current_month", $current_month);
        $query->bindParam("current_month", $current_year);
        $query->bindParam("freelancer_id", $freelancer_id);
        $query->execute();

answered Jun 26 '18 at 16:57

nerdlyist

2,842
2
20
32

Why can't you use wildcards with date/datetimes? http://sqlfiddle.com/#!9/0e255a/2 – user3783243 Jun 26 '18 at 17:07
Have you tried running that against the actual database you have? I just test MSSql and MySql both of which return an empty set. Also, this would be highly inefficient because the DB would be converting everything to string and then doing some kind of string compare where getting the data as shown is much more clear as to the goal and more generally used. – nerdlyist Jun 26 '18 at 17:20
See https://stackoverflow.com/questions/12216033/mysql-select-rows-with-date-like and https://dba.stackexchange.com/questions/96245/mysql-filter-date-column-using-like-or-month – nerdlyist Jun 26 '18 at 17:23
Likewise doing a test of 1000 records with `like` took 42 seconds to return nothing. Return the 1000 rows that existing took less than 1 second using `year(date)` and `month(date)` . – nerdlyist Jun 26 '18 at 17:25
However you may actually be running into this https://stackoverflow.com/questions/22806870/incorrect-datetime-value-database-error-number-1292 – nerdlyist Jun 26 '18 at 17:28
This isn't my question but I know you can use `like` on a `date`/`datetime` and it can be more efficient than the `month`/`year`. The date functions bypass all indices. Can you share the 1k sqlfiddle? I can't see why you wouldn't get a result back (the original provided sqlfiddle returns results) with the `like` unless there were a typo. – user3783243 Jun 26 '18 at 17:31
@user3783243 this is why I asked if the OP used the DB that sqlfiddle worked where my local installs came back empty. I do not see how a string compare could be more efficient could you quantify that (I doubt this is one of those instance anyways I have written many queries like this)? – nerdlyist Jun 26 '18 at 17:36
Yea, probably not relevant here. Seems like there might be 2 issues here. I was using a +90 million row table. Percona engineers told me because of the date function usage my query had to evaluate all 90 million rows everytime. – user3783243 Jun 26 '18 at 17:41
@user3783243 That would seem to be the case when you use the like not when you use the date format. String has no indexes when using a date SQL can assume the format and group them more accurately say by year, month or day and then look through that group. – nerdlyist Jun 26 '18 at 18:18
`When you apply a function on a column it voids usage of the index for lookups, as it would have to apply the same function to ALL the values in the index and hence would be less effective even. Hence the need to use the trick with dates boundaries.` I didn't use the wildcards on both sides either though, with year and month that wouldn't make sense. – user3783243 Jun 26 '18 at 19:26

score 1 · Answer 3 · answered Jun 26 '18 at 17:36

In order to take advantage of indexes, I would instead suggest you use full dates. Just set one date to the beginning of the month, and the other date to the end of the month and search for dates in that range:

$date_start = date('Y-m-1 00:00:00');
$date_end = date('Y-m-t 23:59:59');

$points = 4;
$freelancer_id =4;
$query = $this->db->prepare("UPDATE monthly_limits 
    SET points = :points 
    WHERE freelancer_id = :freelancer_id 
    AND created_at >= :date_start
    AND created_at <= :date_end"
);
$query->bindParam("points", $points);
$query->bindParam("date_start", $date_start);
$query->bindParam("date_end", $date_end);
$query->bindParam("freelancer_id", $freelancer_id);
$query->execute();

Error matching dates in PDO

3 Answers3

Linked