cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Matt_C
Level I

Short Date () creating a locked character column

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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Short Date () creating a locked character column

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" ) ) );
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Short Date () creating a locked character column

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" ) ) );
Jim
Matt_C
Level I

Re: Short Date () creating a locked character column

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

Re: Short Date () creating a locked character column

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

Jim
vince_faller
Super User (Alumni)

Re: Short Date () creating a locked character column

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)
	),
);
Vince Faller - Predictum