Changing character column to date colum

[#1]

Hi all,

assume we have a table with a column of type character with dates of the format "d.m.y". Example given:

Character Dates
05.08.2015
17.03.2016
12.05.2016

I now want to convert the character dates to numeric dates. I know that this is a common issue with lot´s of stuff to read about. However, what i don´t get is the following:

If i right click on the column, got to column info and change the data type to numeric and then select "d.m.y" as format, everything is fine. All dates are converted correctly. But if i run the following script:

Column("Character Dates")  << Data Type(numeric) << Informat("d.m.y") << Format("d.m.y");

or

Column("Character Dates")  << Data Type(numeric) << Format("d.m.y");

only the 1st and 3rd rows are converted correctly. The 2nd row of our example column is null afterwards, presuambly because the format is misinterpreted as "m.d.y".

How is it possible that these approaches (script / manual) lead to different results? And what can i do to make the script approach work?

Thank you!

[#2 · Accepted solution]

Apparently there is an issue in JSL with performing the conversion correctly.  It appears to always use "m/d/y" as the informat.  The script below will do the conversion

Names Default To Here( 1 );

Try( Close( Data Table( "test" ), nosave ) );

dt = New Table( "test",

      Add Rows( 3 ),

      New Column( "Character Dates",

            Character,

            "Nominal",

            Set Values( {"05.08.2015", "17.03.2016", "12.05.2016"} )

      )

);

For Each Row(

      :Character Dates = Substr( :Character Dates, 4, 2 ) || "." ||

      Substr( :Character Dates, 1, 2 ) || "." || Substr( :Character Dates, 7 )

);

Column( "character dates" ) << data type( numeric ) << Format( "d/m/y" );

[#3]

Thank you for your anwser Nelson. I run the script and just got timestamps in the column "character dates". Though, i don´t understand why JMP is ignoring the Format command. My system: JMP 12.2.0 on Windows 7 in Germany (UTC+1; d.m.y).

Currently i´m trying to avoid For Each Row - loops for a script which automatically converts all character columns with dates to numeric dates. I have such a solution but it is just really slow. Hence my question above.

[#4 · Accepted solution]

You could try the Locale Date ​format.

Column("Character Dates")  << Data Type(numeric) << Format("Locale Date");

[#5]

Very, Very nice

[#6]

Thank you MS. The "Locale Date" did the job. However, there is still the For Each loop. But i will pay around to find a different solution.

Meanwhile we figured out, that it is important to install the approbiate ODBC driver if you want to transfer numeric dates from a MS SQL database to JMP. The "SQL Server" driver didn´t let us import numeric dates, but the "ODBC Driver 11 for SQL Server" did.

[#7]

JMP does not always use the m.d.y format at informat.

It uses the m.d.y format if the first date in the first row fits the  m.d.y - regardless of what is in the remaining rows.

If the first row can only fit the d.m.y format, that format is used instead. So when testing, everything may seem fine until the imported row fits into m.d.y format... In the US this is of course not a problem as all dates are in m.d.y.

It is still the case in JMP 15.1.0 and I hope this will get fixed some time as it is still a pain working with imported dates in JMP although there has been some improvement in .csv and excel imports.