CODEWITHSUNDEEP
phpmysqlsqldate
Hüseyin ASLIM
Hüseyin ASLIM

Reputation: 153

SQL Where Date Condition

I want get sales data on between the two dates but it does not work correctly SQL query.

saledate column

  if($_GET['startdate'])
  {
    $startdate = date('Y-m-d H:i:s', strtotime($_GET['startdate']));
  }
  else
  {
    $startdate = 'NOW()';
  }
  if($_GET['enddate'])
  {
    $enddate = date('Y-m-d H:i:s', strtotime($_GET['enddate']));
  }
  else
  {
    $enddate = date('Y-m-d H:i:s', strtotime('-1 month'));
  }
  $db->setTrace('true');

  $sales = $db->rawQuery('SELECT * from sales where (saledate BETWEEN ? AND ?) AND deletestat = ?',  [$startdate, $enddate, '0']);
  print_r($db->trace);

I am using joshcam/mySQLi class for sql queries.

How to get data on between the two dates?

Upvotes: 1

Views: 276

Answers (1)

Perry
Perry

Reputation: 11700

The mysql between function works different than you expect.

The between function expect the first parameter to be the min value en the second parameter to be the max value. See the documentation: http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between

If you change this line:

$sales = $db->rawQuery('SELECT * from sales where (saledate BETWEEN ? AND ?) AND deletestat = ?',  [$startdate, $enddate, '0']);

To this:

$sales = $db->rawQuery('SELECT * from sales where (saledate BETWEEN ? AND ?) AND deletestat = ?',  [$enddate, $startdate, '0']);

Than it will work.

Upvotes: 1

Related Questions