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!
Solved! Go to 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.
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
Worked perfectly - thank you!
@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!