cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
View Original Published Thread

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

JulieSAppel
Level IV

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?

jpeg.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User


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

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

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

Craige

View solution in original post

11 REPLIES 11
Craige_Hales
Super User


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

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 formatOutput 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.

Craige
JulieSAppel
Level IV


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

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

Craige_Hales
Super User


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

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.

Craige
JulieSAppel
Level IV


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

Thanks - that helps
txnelson
Super User


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

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);
Jim
Craige_Hales
Super User


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

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

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

Craige
JulieSAppel
Level IV


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

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

?

txnelson
Super User


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

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

Jim
Craige_Hales
Super User


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

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 monddyyyyinformat yyyymmdd format monddyyyy

Input Format available after choosing FormatInput 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 pageText 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.

 

Craige