topic Re: How do I convert a nummeric string into a date? in Discussions

How do I convert a numeric "string" into a date?

JulieSAppel — Fri, 30 Mar 2018 18:18:16 GMT

I´m a new user and I´m trying to convert a nummeric string into a jmp date format.

I tried creating a new column by concatenating a character string so that could at least get my desired "ddmmyyy" output:

(Substr( :START_DATE, 7, 2 ) || Substr( :START_DATE, 5, 2 )) ||Substr( :START_DATE, 1, 4 ) but that didnt get´me any further.

Any ideas on this?

Re: How do I convert a nummeric string into a date?

Craige_Hales — Tue, 06 Mar 2018 12:54:58 GMT

You are really close; you'll need char(start_date) to get a string from the number. I tried a slightly different approach for a column formula

Informat( Regex( Char( :START_DATE ), "(\d\d\d\d)(\d\d)(\d\d)", "\1-\2-\3" ), "ymd" )

and set the column's format

Output format different from input format

JMP's preferred internal representation for dates is the JMP date-time value which is a numeric value that can be formatted with a number of different formats.

Re: How do I convert a nummeric string into a date?

txnelson — Tue, 06 Mar 2018 13:00:59 GMT

Here is a simple script to do the same thing that Craige did

Names Default To Here( 1 );
dt = Current Data Table();

For Each Row(
	:start_date = Date MDY(
		Num( Substr( Char( :start_date ), 5, 2 ) ),
		Num( Right( Char( :start_date ), 2 ) ),
		Num( Left( Char( :start_date ), 4 ) )
	)
);
dt:start_date << format("ddmmyyyy",12);

Re: How do I convert a nummeric string into a date?

Craige_Hales — Tue, 06 Mar 2018 13:10:26 GMT

Jim - I got started wrong. The yyyymmdd format works without regex or substr:

Informat( Char( :START_DATE ), "yyyymmdd" )

Re: How do I convert a nummeric string into a date?

JulieSAppel — Thu, 15 Mar 2018 10:06:15 GMT

Thanks - this works. Im not entirely sure I understand it completely though.

Is the "(\d\d\d\d)(\d\d)(\d\d)" stating the input format?

What is "\1-\2-\3" doing?

Thanks for helping out

Re: How do I convert a nummeric string into a date?

JulieSAppel — Thu, 15 Mar 2018 10:13:12 GMT

Thanks for sharing. I understand this better, however it only works for me in the first row. After that it just returns "missing values".

Also what does the "12" do in

dt:start_date << format("ddmmyyyy",12);

Re: How do I convert a nummeric string into a date?

Craige_Hales — Thu, 15 Mar 2018 11:39:14 GMT

this post is about the (\d\d) part. The parens create groups and the \d matches a single digit.

this post is about the \1-\2-\3. those are back references and extra text (the hyphens) inserted into the result string. The numbers refer to the matching open parenthesis.

Regex is a powerful tool, there are many tutorials on the internet.

This example is overkill for the problem at hand; JMP has a date informat that already matches the format of your numbers after you convert them to characters.

Re: How do I convert a nummeric string into a date?

txnelson — Thu, 15 Mar 2018 11:55:30 GMT

1. Given the sample data table you provided, the script that I posted works fine. See attached file

2. the 12 is the width of the format display. See:

Help==>Scripting Index==>Data Table==>Column Scripting

for a descripting and examples

Re: How do I convert a nummeric string into a date?

Craige_Hales — Thu, 15 Mar 2018 12:40:43 GMT

There are some differences between how you use Jim's script and the column formula.

The column formula makes a new column based on the old data. If new rows are added, the formula runs automatically to convert the date values. The old data column can't be deleted unless the formula is removed first.
The script is run once, from a script editor. Jim's script converts the column in place, changing the number from the original representation to a JMP date-time representation. If more data is added after the script is run, it should be added using the JMP date-time format the column expects.

I think the question you might want answered is "how did I get these dates JMP doesn't like, and what would a better approach be?"

When you imported this data into JMP, possibly from a CSV file, or when you typed it in to a fresh table, JMP saw a number and made a numeric column. To make JMP see a date in 20180315 rather than 20,180,315 you need to tell JMP to interpret the data as yyyymmdd format. You can do that with the column proerties in a data table and specify both the informat and the format.

informat yyyymmdd format monddyyyy

Input Format available after choosing Format

If you are using text file import to get the data from a CSV file, you can also specify a format for the column using the red triangle above the column on the second page of the preview wizard.

Text import preview wizard second page

Finally, JMP's date-time values are represented as the number of seconds since 1Jan1904. https://www.jmp.com/support/help/14/date-time-functions.shtml has a lot more information. If you see a huge number that makes no sense, it probably needs a format. You can change the output format, showing 03 or Mar, since the internal representation stays the same.

Re: How do I convert a nummeric string into a date?

JulieSAppel — Thu, 15 Mar 2018 12:53:10 GMT

Thanks - that helps

Re: How do I convert a nummeric string into a date?

JulieSAppel — Thu, 15 Mar 2018 13:10:08 GMT

Actually, when I run it on the test file I get missing data in all of the rows. I use JMP 12 - can that be the reason?

Re: How do I convert a nummeric string into a date?

JulieSAppel — Thu, 15 Mar 2018 13:17:22 GMT

No sorry it works now

Thanks for the help