AnsweredAssumed Answered

How can I use SugarQuery to get Account records where a custom datetime field contains one specified date?

Question asked by Artis Plocins on Jul 19, 2017
Latest reply on Jul 20, 2017 by Artis Plocins

I have two custom datetime fields in the Accounts module and I want to use the date I get from the bean in a logic hook to get records with matching dates in the custom datetime fields.

// From an after_save logic hook in the Accounts module:

$td = new TimeDate(); // SugarCRM extended date/time tools
$j_field = 'j_custom_datetime_field_c';
$f_field = 'f_custom_datetime_field_c';

// I tried converting the datetime object to a date string first
$date = $td->fromString($bean->$field);
$date = $date->format('Y-m-d');

$sq = new SugarQuery();
$sq->from(BeanFactory::newBean('Accounts'));
$sq->select(array('id', $j_field, $f_field));
// I want to get the row if either of the datetime fields match the same date
$sq->where()->queryOr()->starts($j_field, $date)->starts($f_field, $date);
$sq->orderBy($j_field)->orderBy($f_field);
$results = $sq->execute();

I always get 0 results.

// Should I use this instead of where()->queryOr()->starts(...)?
// Can I use whereOr() to combine two dateBetween() for both $j_field and $f_field?
$sq->where()->dateBetween($j_field, array($date, $date));

When I tried a query in phpMyAdmin like what I'm trying to do in the first code example I get no results, but this does return some results:

SELECT id_c, j_custom_datetime_field_c, f_custom_datetime_field_c
FROM `accounts_cstm`
WHERE DATE_FORMAT(j_custom_datetime_field_c, '2018-01-17');

So how can I use SugarQuery to get rows where both custom datetime fields match the date that is in a custom datetime field of the bean in the logic hook?

Outcomes