topic Re: Short Date () creating a locked character column in Discussions

Short Date () creating a locked character column

Matt_C — Tue, 03 Dec 2019 23:47:02 GMT

When I create a "date" column in the script below it creates a locked (so I can not change the format) column. Short Date() in the formula looks to be the issue.

The time column stays as numeric after the formula is applied.

How do I get the "Date" to stay numeric?

The date is later in a calender() to set the calender to show only days there is data present.

_____________________________________________________

//Open
dt_run = Open( "data.jmp");

dt_run << New Column( "Date", Numeric, Continuous, Format("m/d/y"), Input Format( "m/d/y h:m:s"));
Column( "Date") << Add Column Properties( Formula( Short Date( :Name( "Date & Time" ) ) ) );

dt_run << New Column( "Time", Numeric, Continuous, Format("h:m"), Input Format( "m/d/y h:m:s"));
Column( "Time") << Add Column Properties( Formula( Time Of Day( :Name( "Date & Time" ) ) ) );

Thanks

Matt

Re: Short Date () creating a locked character column

txnelson — Wed, 04 Dec 2019 04:54:06 GMT

The issues you are having are with the understanding of what the Short Date() function returns. It does not return a JMP date value, which would be the number of seconds since 12:00am, January 1, 1904, but rather, it returns a literal string of the date part of a JMP date value. To illustrate this, the following JSL displays what Short Date() returns:

show(short date(today()));

and it returns

Short Date(Today()) = "12/03/2019";

And since in your JSL it returned a literal string, it changed the Date column to a character column.

Really, what you want is to use the DateMDY() function to create the date value.

Below is a reworked version of your code that does what I believe you need:

Names default to here(1);
dt_run = new table("data",add rows(1), new column("Date & Time",formula(today()),format("m/d/y h:m:s")));

dt_run << New Column( "Date", Numeric, Continuous, Format("m/d/y"));
Column( "Date") << set Formula( DateMDY(Month(:Name( "Date & Time" )),Day(:Name( "Date & Time" )),Year(:Name( "Date & Time" )) ) );

dt_run << New Column( "Time", Numeric, Continuous, Format("h:m"));
Column( "Time") << set Formula( Time Of Day( :Name( "Date & Time" ) ) );

Re: Short Date () creating a locked character column

Matt_C — Wed, 04 Dec 2019 16:12:09 GMT

Jim,
Thanks for such a well explained answer. Your posts are always informative, educational and easy to follow.
Matt

Re: Short Date () creating a locked character column

txnelson — Wed, 04 Dec 2019 16:40:01 GMT

Given a little more thought on your JSL.......you could use the following to get your Date column populated

Column( "Date") << set Formula( informat(Short Date( :Name( "Date & Time" ) ), "mm/dd/yyyy") );

It just uses the Informat() function, which takes a literal string and returns a numeric JMP Date value

Re: Short Date () creating a locked character column

vince_faller — Wed, 04 Dec 2019 16:44:49 GMT

You can also do it using only the number by changing date time from seconds to days and flooring.

Names Default to Here( 1 );
dt = New Table( "Test",
	Add Rows( 1 ),
	New Column( "DateTime",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m:s", 23, 0 ),
		Formula( Today() ),
	)
);


dt << New Column("Date", 
	Numeric,
	"Continuous",
	Format( "m/d/y", 23 ),
	Formula( 
		num_s_per_day = 24*60*60; // # seconds/day
		Floor(
			:DateTime // take the date time which is a number in seconds
			/ num_s_per_day // turn it into days by dividing 
		) // this will round down to the day
		* num_s_per_day // turn it back into seconds (a date time)
	),
);