Hide and Exclude dates column faster

[#1]

Hello,

I have a JMP table with 76K rows with a column named "sortDateColumn" that has dates in this format:

09/24/2020 11:29:25 AM

in my code I have this line that takes 4 seconds to run

minDate = "02/16/2021 12:23:09 AM";
dt << Select where( Column( sortDateColumn )[] <= Informat( minDate, "MDYHMS" ) ) << Hide and Exclude();

is there a faster way to run this line? since doing this a few times slows my whole code down by like 1 minute

if i do on another row 

dt << Select Where( :"age" < 650 );

it takes less then 0.1 seconds but with the dates it takes a lot more

[#2 · Accepted solution]

There are a few errors with your code but I think the one that is slowing you down the most is that there is no informat "MDYHMS". That's causing many errors to be written to the log and that's what taking so long. Take a look in your log and see if I'm correct.

Try this instead:

minDate="02/16/2021 12:23:09 AM";
current data table() << Select where( :column 1 <= informat(minDate, "m/d/y h:m:s" )) << Hide and Exclude();

However, even that's more than you need. You can avoid the informat all together by using a datetime constant (you can read about them in Using dates, times, datetimes and durations in JMP).

minDate="16Feb2021 12:23:09 AM";
current data table() << Select where( :column 1 <= informat( minDate ) << Hide and Exclude();

Finally, you had a spurious [] after your Column() designation. I don't think that's useful here.

[#3]

this did fix the issue, it now runs in 0.3 seconds thanks!

[#4]

I do not know how much this change will speed up this step, but one of these versions might be worth a little improvement.

// call Informat() function once for constant time point
minDate = Informat( "16Feb2021 12:23:09 AM" );
Current Data Table() << Select Where( :column 1 <= minDate ) << Hide and Exclude();

// use literal value for constant time point
Current Data Table() << Select Where( :column 1 <= 16Feb2021:12:23:09 ) << Hide and Exclude();