Compare a mysql datetime field with string datetime

Question

I have a function as below to extract records from mysql database.

function getMember($read, $selected_joined_date) {
  $sql = "SELECT first_name, last_name, member.joined_date
          FROM user 
          INNER JOIN member USING (user_id)
          WHERE user.joined_date > $selected_joined_date
          ORDER BY joined_date";
  return $read->fetchAll($sql);
}

It dipslays error message displayed as

'SQLSTATE[42000]: Syntax error or access violation.

The field 'joined_date' is datetime format in table and $selected_joined_date is string format.

How do I compare the different type of datetime format ?

Where is the code that executes your $sql query? And how does the value of `$selected_joined_date` look like? post `echo $selected_joined_date;` — peterm, May 07 '13 at 19:42
example of output format of $selected_joined_date is 2013-03-25 12:30:54 — user2359714, May 07 '13 at 19:51
Shouldn't `$selected_joined_date` have quotes around it? `WHERE user.joined_date > '$selected_joined_date'` — gen_Eric, May 07 '13 at 20:25
What is `$read`? Are you using PDO? Because if you are, you don't pass `fetchAll` a string, that's incorrect. — gen_Eric, May 07 '13 at 20:31

score 1 · Answer 1 · answered May 07 '13 at 20:26

You need quotes around your strings in your query.

$sql = "SELECT first_name, last_name, member.joined_date
        FROM user 
        INNER JOIN member USING (user_id)
        WHERE user.joined_date > '$selected_joined_date'
        ORDER BY joined_date";

progrrammer · Answer 2 · 2013-05-07T20:31:02.487

0

You can use Date() function provided by mysql to convert string format to Date

 mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

And your query will look like: Got MySql Solution, you can use str_to_date function as;

 mysql>select str_to_date('2012-10-12 10:20:30','%Y-%m-%d %H:%i:%s');

So your Sql String will be,

$sql = "SELECT first_name, last_name, member.joined_date
      FROM user 
      INNER JOIN member USING (user_id)
      WHERE user.joined_date >str_to_date('$selected_joined_date','%Y-%m-%d %H:%i:%s')
      ORDER BY joined_date";
// Provided that string selected_joined_date is in format 2012-10-12 10:20:30

edited May 07 '13 at 20:31

answered May 07 '13 at 19:42

progrrammer

4,475
2
30
38

the joined_date is datetime format. so need to compare the time as well – user2359714 May 07 '13 at 19:52
Code changed, See If it solves.[using this post](http://stackoverflow.com/questions/8063057/convert-this-string-to-datetime?answertab=oldest#tab-top) – progrrammer May 07 '13 at 19:59
What is your datetime string format,? See the Note section above. – progrrammer May 07 '13 at 20:05
how do i execute mysql>select str_to_date('2012-10-12 10:20:30','%Y-%m-%d %H:%i:%s'); ? – user2359714 May 07 '13 at 20:28
I am giving an example and You can execute this in cli, You dont need to execute the mysql> line. just copy your sql string – progrrammer May 07 '13 at 20:30

score 0 · Answer 3 · answered May 07 '13 at 20:35

Mysql likes datetimes in the format "2013-05-07 15:30:00", php can handle just about anything.

You would convert $selected_join_date to that format like so:

$selected_join_date = date("Y-m-d H:i:s", strtotime($selected_join_date));

assuming $selected_join_date is in one of php's accepted formats: http://www.php.net/manual/en/datetime.formats.php

Then you can pass the string to mysql and it will happily compare them. The variable (and therefore the string date time) needs to be inside quotes in the sql, as Rocket Hazmat pointed out.

Danijel · Answer 4 · 2013-06-05T18:59:44.057

I made a little test out of curiosity whether php date format must be the same as mysql datetime format. It turned out that format does not matter. If a PHP variable was in Mysql Date and Time format, search was successful.

default mysql datetime format:

YYYY-MM-DD HH:MM:SS

formats used for searching database:

'Y-m-d H:i:s'
'Y/m/d H:i:s'
'Y/m/d'
'YmdHis'
'Ymd'

date formatted with DateTime::createFromFormat() function

The problem, except that $selected_joined_date variable in query needs to be inside quotes, seems to be elsewhere.

Compare a mysql datetime field with string datetime

4 Answers4