Select range of rows around user input start date

[#1]

I am working with a large data set spanning many years, which can be simplified to two columns named "ID" and "Test_date" . Date is formatted as 'm/d/y', and the data table is sorted chronologically. I would like to select all of the rows where the test date is within one year (before or after) of the Test_date specified by the user, and then delete all other rows from the data table.

Below is what I have tried so far. I have created the upper and lower date limits for a input ID, but am unable to select the dates within that range using select where(). Instead, I get the error "The argument to SelectWhere did not evaluate to true or false, it is [0]... ". Any help on this would be much appreciated.

dt << current data table();
dt << new column("T_YEAR", numeric, continuous, set each value(:Test_date / (365*86400)));

setpoint =  ;//user specified numeric value
set_row= dt << get rows where(:ID == setpoint);

set_date = dt:T_YEAR[set_row] ;
set_min = (set_date - 1);
set_max = (set_date + 1);


dt << select where(:T_YEAR >= set_min & :T_YEAR <= set_max )
   << invert row selection
   << delete rows;

[#2]

Datetimes are in units of seconds, so you'll to do something like this:

set_min = (set_date - (1 * 365 * 24 * 3600));
set_max = (set_date + (1 * 365 * 24 * 3600));

[#3]

Yes, that's essentially what I did by creating a new column "T_YEAR" with the date converted to years using Test_date/ (365*86400). I've tried both, and it works either way. The issue seems to occur when using set_min and set_max variables in select where() to find all rows between those values.

[#4 · Accepted solution]

@ben_T, it looks like your reply and mine overlapped.

This works for me.

dt=open("$SAMPLE_DATA\TechStock.jmp");

set_date=datemdy(12, 15, 2000);

set_min = (set_date - In Days(5));
set_max = (set_date + In Days(5));


dt << select where(:Date >= set_min & :Date <= set_max )
   << invert row selection
   << delete rows;

Note that I had to change to In Days() instead of years only because the sample data didn't have more than one year in it, but other than that this should work for you.

[#5]

Lots of ways to do this.

You can use the In Years() function to return the JMP datetime value for a number of years, or the Date Increment() function to add or subtract a number of years.

Also, you could get the Year() of your set date, add 1 to that and use the Date MDY() function to give you the JMP date value.

set_min = (set_date - In Years(1));
set_max = (set_date + In Years(1));

//or

set_min = Date Increment(set_date, "Year", -1, "actual");
set_max = Date Increment(set_date, "Year", 1, "actual");

//or

set_min = Date MDY( Month( set_date ), Day( set_date ), Year( set_date ) - 1 );
set_max = Date MDY( Month( set_date ), Day( set_date ), Year( set_date ) + 1 );