Table query date interval

I wasn’t able to find any previous documentation on date intervals for a relative date interval. Do I need to generate a report using that criteria first, in order to pull this? If not, can you help identify the format? Ideally, looking for something to pull last30, last 90, etc.

Thanks!

Depends on what you are wanting reports on. Date ranges apply to a lot of information. Generally when you edit the search criteria, a date range is available. You can then save that as a saved search to re-call it up later.

Thanks John. My inital challenge occured when attempting to pull a report of new contacts created in the past 2 months. To accomplish that currently, I have simply hard coded the 3/1/18 date as seen here:

IFS_WhereDate

I do realize that I could create a saved search, and perhaps I need to do that. I was hoping I could find a way to simply format a last60 or last month format from current day date in a dynamic manner.

Thanks!

So if you’re coding a hard coded value then you can code a value that is 30/60 days prior based on the current date. We do this in PHP all the time by merging today’s date with the operator (~>~)

Do you have a reference to the formatting, so I can try that? Sorry, not a php expert.

So the trick is in getting the date/time in a ‘timestamp’ format and then doing the math and finally converting that back to a date/time. PHP has functions to help do this:

// first let's get today's date '$now'
$now=date('Y-m-d H:i:s', time());

// next create an object for the date
$dateObj=new DateTime($now);

// next let's get the timestamp which expresses time in seconds since jan, 1st 1970 (epoch time)
$dateObjSeconds=$dateObj->getTimeStamp();

// now to subtract 30 days measured in seconds
$thirtyDaysAgo=30 * 24 * 60 * 60;
$then=$dateObjSeconds-$thirtyDaysAgo;

// finally, let's convert it back to a date format we can submit the query for with the > operator added
$qDate='~>~'.date('Y-m-d H:i:s', $then);

// lastly, do the query
$returnArray=$myAppObject.dsQuery('Contact', 1000, 0, array('DateCreated'=>$qDate), $returnFieldsArray);

While each part’s details may vary for your use, the process as I’ve laid it out here is how to determine a specific date range query. You can replace the 30 day math with 60 days or whatever suits your needs.

1 Like