Date function in jsl.

[#1]

I have a table with that has time stamp for all the rows.

But I need only last 7 days data. Is there an easy way to delete all data that is past last 7 days.

New to jsl and any inputs will be helpful.

[#2]

dt << select where( today() - In Days( 7 ) < :Your date column );
dt << delete rows;

This will select all rows where your date column(replace its name in the first line of the JSL), is less than the current datetime - 7 days.  Then the delete rows will delete all selected rows.

If you want the rows to be deleted to be 7 days prior to midnight, not 7 days before this point in time (today()), then you need to subtact the current time from the today() functions value.

dt << select where( today() - Time of Day( today() ) - In Days( 7 ) < :Your date column );
dt << delete rows;

[#3]

This piece of script isnt working for me.

the " today() - Indays(7) part works but something wrong with Date comparision.

Debugger doesnt show any error but nothing happens when I run this. I am using JMP 12. Is that a problem?

[#4]

I don't have JMP 12 available to me to verify if In Days() function is available.  However, since all In Days() is doing is determining the number of seconds in a day, and then multiplying the result times the number in the (7), you can just change the formula to:

today()-7*60*60*24 

[#5]

Thanks for the response.As I mentioned above, I created a new column and printed today() - In Days(7) which works perfectly fine.

After this if I try comparing 2 columns,I dont see any result/error. All date columns are numeric.

[#6]

Can you show the exact code you are using that is not working, and could you supply a sample of your data table?

[#7]

Here's the code -

Clear Symbols();
Delete Symbols();
clear globals();

//Open master data list
S = Open("C:\Enthu\sample.xlsx","invisible");
 
current data table(S);
New Column( "START_SHORT_DATE", Numeric, Formula( Short Date( :start date ) ), );

New Column("Todays Date", Character,Formula(Abbrev Date(today())));

New Column("Ref Date", Character,Formula(Abbrev Date(today()-In Days(7))));
S << select where( :Ref Date < :START_SHORT_DATE );

[#8]

The problem is that the functions Short Date() and Abbrev Date() return character strings not date values. You can't compare a date value (which is numeric) to a character string.

Try this instead:

New Column( "START_SHORT_DATE",
	Formula( :start date - Time Of Day( :start date ) ),
	Format( "m/d/y", 10 ),
);
New Column( "Todays Date",
	Formula( Today() - Time Of Day( Today() ) ),
	Format( "m/d/y", 10 )
);
New Column( "Ref Date",
	Formula( Today() - In Days( 7 ) ),
	Format( "m/d/y", 10 ), 

);

current data table() << select where( :Ref Date < :START_SHORT_DATE );

[#9]

I copied the exact same code and tried running.All columns are created properly.But the following line doesnt seem to work -

"

current data table() << select where( :Ref Date < :START_SHORT_DATE );

Nothing gets selected.I tried using greater than , less than and equal to operators.Nothing seems to work.

[#10]

Sorry I didn't catch this earlier. You'll need a Run Formulas() to ensure that all formulas are evaluated before the Select Where().

Clear Symbols();
Delete Symbols();
clear globals();

//Open master data list
S = Open("~/Downloads/sample.xlsx");

New Column( "START_SHORT_DATE",
	Formula( :start date - Time Of Day( :start date ) ),
	Format( "m/d/y", 10 ),
);
New Column( "Todays Date",
	Formula( Today() - Time Of Day( Today() ) ),
	Format( "m/d/y", 10 )
);
New Column( "Ref Date",
	Formula( Today() - In Days( 7 ) ),
	Format( "m/d/y", 10 ), 

);
s << run formulas();
s << select where( :Ref Date < :START_SHORT_DATE );

BTW: You don't really need all the formula columns to do this.

Clear Symbols();
Delete Symbols();
Clear Globals();

//Open master data list
S = Open( "~/Downloads/sample.xlsx" );

s << select where(
	(Today() - Time Of Day( Today() ) - In Days( 7 )) < :start date - Time Of Day( :start date )
);