topic Re: Date column not sorting as it is a character in Discussions

Date column not sorting as it is a character

jearls11 — Sat, 10 Jun 2023 23:26:05 GMT

I have a dataset and trying to sort by date.
I am having issues I have tried to informat the column as it been read in as a character.

But with no success. Have attached the dataset without the informat

Re: Date column not sorting as it is a character

jthi — Fri, 12 Feb 2021 19:34:12 GMT

I would suggest checking out Data Table Tools Add-in by @brady_brady

Re: Date column not sorting as it is a character

txnelson — Fri, 12 Feb 2021 19:35:34 GMT

To turn this into proper JMP Date/Time numeric value:

Right click on the column header and select "Column Info"
Change the Data Type from Character to Numeric
Change the Modeling Type from Nominal to Continuous
Select from the Format..........Time=>m/d/y h:m:s
Click OK

Re: Date column not sorting as it is a character

Georg — Sun, 14 Feb 2021 17:29:36 GMT

Please screenshot,

as @txnelson pointed out, you have to convert it to a proper continuous time value to work with it.

The display format is not important, it is only how the internally stored datetime is displayed.

But it is essential how the given string is interpreted, in your case you need to specify the appropriate informat, see screenshot and table script.

In that case I copied the column, because after conversion the original string is lost. In my case I had also to switch to JMP locales under preferences, Window specific, to see the proper datetime format (I have german windows system).

New Table( "time_sort",
	Add Rows( 5 ),
	New Column( "Proc Time",
		Character,
		"Nominal",
		Set Values(
			{"1/12/2021 11:44:32 AM", "1/12/2021 11:44:32 AM",
			"1/12/2021 11:44:32 AM", "1/12/2021 11:44:32 AM",
			"1/12/2021 11:44:32 AM"}
		),
		Set Display Width( 177 )
	),
	New Column( "copy_date",
		Numeric,
		"Continuous",
		Format( "Locale Date Time h:m:s", 23, 0 ),
		Input Format( "m/d/y h:m:s", 0 ),
		Set Selected,
		Set Values( [3693296672, 3693296672, 3693296672, 3693296672, 3693296672] ),
		Set Display Width( 183 )
	)
)