current_date-30 in querry

nlarson

I have used CURRENT_DATE+/-'Value' in rules and it seems to worked as expected, however the query below expecting only records with a Measured Date in the last 30 days, but I get 'all time'. I tried to see what was happening in the logs, but even on full the query does not even register. Shoud this querry work, or do I need to format it differently than a rule?

FIND Measurements
WHERE Measurements.MetricReference.IsKPI='Yes'
AND Measurements.Status<'95'
AND Measurements.MeasureDate<=CURRENT_DATE
AND Measurements.MeasureDate>=CURRENT_DATE-30
ORDER BY Measurements.MeasureDate DESC

tford

I know this works:

FIND Customer WHERE Customer.Dt<=CURRENT_DATE

For the date math, you need to use DATE_ADD or some other function.

rocketman

Ayou can improve readability by using between

Measurements.MeasureDate BETWEEN DATE_ADD(CURRENT_DATE,-30) AND CURRENT_DATE

nlarson

many thanks.