Filtering rolling date range

[MCDR929]

I have seen many posts similar to this, but none are working and I'm not sure why.

I have a large data set, and I need it to pull only the last 6 months of data every time it runs.  The date field is CREATE_DATE_TIME, in format YYYY-MM-DD hh:mm:ss.

So I need a formula in my filter that will only pass along the last 6 months in the create_date_time field.

I feel like this should be really simple, yet I've been banging my head on my keyboard all day.  Please help!

[JosephSerpis · Accepted solution]

Hi @MCDR929 try this syntax in a formula tool datetimediff(DateTimeNow(),DateTimeParse([create_date_time ],"%Y-%m-%d %H:%M:%S"),"Months") it takes your date field parses to ensure its a data and does a datatimediff from Date Time Now function and gives back the results in months. So you can then easily filter anything within the last 6 months.

[jrgo]

Hi @MCDR929 

Try using this expression in your Filter tool.

TODATE([CREATE_DATE_TIME]) >= TODATE(DATETIMEADD(DATETIMEFIRSTOFMONTH(), -6, 'months'))

This assumes that you are looking to filter to the last 6 FULL months (>= 2019-03-01), not just 6 months from today (>= 2019-03-10). However, if you were trying to do the latter, change DATETIMEFIRSTOFMONTH to DATETIMETODAY.

Best,

Jimmy
Teknion Data Solutions

[MCDR929]

Worked perfectly - thank you!

[MCDR929]

@jrgo - This didn't work for me, I think because of the reason @JosephSerpis pointed out.  Something screwy with my date format, maybe.  But I got it, finally.  Thanks for the assistance!