Change Date Time format in JSL not working (JMP15)

[#1]

Hi,

Recently, I'm having an issue in converting the date time format from character to numeric date.

I want the character date set to numeric d/m/y h:m:s.

I've converted it manually & copy the script from table.

But when I run the script, my date return to m/d/y h:m:s and there's missing value.

I can't figure out why it's not working.

I'm using JMP 15

:Wirebond End Date
<< Data Type( Numeric)
<< Modeling Type(continuous)
<< Format( "d/m/y h:m:s", 22, 0 );
<< Input Format( "d/m/y h:m:s", 0 );

[#2]

In your JSL, you have a ";" after the << Format(.....) that should not be there

[#3]

I've removed the ";".

:Wirebond End Date
<< Data Type( Numeric) 
<< Modeling Type(continuous) 
<< Format( "d/m/y h:m:s", 22, 0 ) 
<< Input Format( "d/m/y h:m:s", 0 );

Still same result. Could it be some bug? :(. 

[#4]

My guess is that the column is treated on such a way, that it is first converted to d/m/yyyy format and then to m/d/yyyy and this will cause missing values (nightmare for me to test as I use different date-time format and JMP doesn't like me testing with different ones).

Also, JSL I get from JMP is different than the one you are using (see that everything is inside Data Type() besides setting Modeling Type.

Data Table("Untitled 3"):Time2 << Data Type(
	Numeric,
	Format("d/m/y h:m:s", 22, 0),
	Input Format("d/m/y h:m:s", 0)
) << Set Modeling Type("Continuous");

Maybe this could be causing some issues as format is being set before input format in your script (not sure if it behaves this way)?

[#5]

Hi @jthi 

I think you are right.

Seems date input format in d/m/y (character) can't convert to d/m/y (numeric)

When the input format data is in m/d/y, the character column can be converted to numeric using JSL with flying colours.

I've asked our IT team to change my automated data input format to m/d/y instead of d/m/y since I need to fix the issue urgently.

JMP15 - JSL can't convert d/m/y (character) to d/m/y (numeric) but it can be done manually at data table.

Weird though.

Thanks anyway! At least I know I'm not the only one cracking heads for different date time format issue.

[#6]

The fool proof way is to create an extra column with formula (replacing :time with the correct source column name):

in format(:time, "Format Pattern", "<DD></><MM></><YYYY> <hh24><:><mm>:<ss>")

and then set that new column to display in whatever format you prefer:

New Column( "Fixed time format",
	Numeric,
	"Continuous",
	Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ), //Change to whatever output format you like
	Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
	Formula(
		Informat(
			:Time,
			"Format Pattern",
			"<DD></><MM></><YYYY> <hh24><:><mm>:<ss>"
		)
	)
)

If you then remove the formula, you can deleter the orginial column afterwards:

:Fixed time format << Delete Formula;
current data table() << Delete Columns(:time);