Drupal 7: Database query with MySQL Date functions

The MySQL is the database which stores any type of data in Database. Using the MySQL we can able to store date-related fields. The MySQL will support Date functions to retrieve and format database date field values. In Drupal also we can able to add date functions in our queries with help of where() function in the database object. The below is the syntax and example of usage of the where() function.




$retrive = db_select('user', 'u');
$retrive->fields('u', array('uname', 'mail', 'uid'));
$retrive->fields('dob', array('field_date_of_birth_value'));
$retrive->leftJoin('field_data_field_date_of_birth', 'dob', 'dob.entity_id=u.uid');
$retrive->condition('dob.entity_type', 'user');
$retrive->condition('dob.deleted', 0);
$retrive->where("(DATE_FORMAT(ADDTIME(dob.field_date_of_birth_value, SEC_TO_TIME(-14400)), '%Y-%m-%d') >= :filter_date)", array(':filter_date' => $filterDate));
$retrive->orderBy('dob.field_date_of_birth_value', 'ASC');
$result = $retrive->execute();
$result = $result->fetchAll();