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?
Jim - I got started wrong. The yyyymmdd format works without regex or substr:
Informat( Char( :START_DATE ), "yyyymmdd" )
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
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.
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
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.
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 - I got started wrong. The yyyymmdd format works without regex or substr:
Informat( Char( :START_DATE ), "yyyymmdd" )
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);
?
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
There are some differences between how you use Jim's script and the column formula.
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.
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.
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.