SugarQuery condition on Month?

I have the need to find records where the expiration date is after the end of next year and the expiration date month is less than this month.

I can use 

$sugarQuery->where()->gt('expiration_date', $next_year_end)

but how can I check for the month?

Am I stuck with a SQL query or can I leverage SugarQuery somehow?

Thanks,
Francesca

Parents
  • Hi Francesca,


    Thanks for bringing this up!
    If I understand your question correctly, you might be able to use the dateRange() function within SugarQuery to handle the month-based filtering.


    According to the SugarCRM Developer Guide:

    support.sugarcrm.com/.../

    If “less than this month” refers to dates within the last 30 days, you could use:

    $SugarQuery->where()->dateRange('expiration_date','last_30_days');

    If instead you mean “within this current month,” then this should work:

    $SugarQuery->where()->dateRange('expiration_date','this_month');

    Hope this helps! Let me know if it gives you the results you’re looking for.

    Cheers,

    André



  • Sorry, I didn't explain myself well...

    I was wanting to query for "records expiring this month ANY year after next year"

    So if I run it in May I want records expiring in May 2027, May 2028, May 2029... etc.

    and in June I want records expiring in June 2027, June 2028 ...etc

    So I just used a query, it's easier than building out all the dates...

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    $query = "select id
    from wcont_cont
    where cont_status = 'Active'
    and YEAR(expiration_date) > ?
    and MONTH(expiration_date) = ?
    and deleted = 0
    ";
    $params = array($next_year, $this_month);
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Reply
  • Sorry, I didn't explain myself well...

    I was wanting to query for "records expiring this month ANY year after next year"

    So if I run it in May I want records expiring in May 2027, May 2028, May 2029... etc.

    and in June I want records expiring in June 2027, June 2028 ...etc

    So I just used a query, it's easier than building out all the dates...

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    $query = "select id
    from wcont_cont
    where cont_status = 'Active'
    and YEAR(expiration_date) > ?
    and MONTH(expiration_date) = ?
    and deleted = 0
    ";
    $params = array($next_year, $this_month);
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Children
No Data