Subscribe Bookmark RSS Feed

Changing character column to date colum

vkessler

Community Trekker

Joined:

Dec 23, 2015

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!

5 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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

Jim
vkessler

Community Trekker

Joined:

Dec 23, 2015

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.

ms

Super User

Joined:

Jun 23, 2011

You could try the Locale Date ​format.

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

txnelson

Super User

Joined:

Jun 22, 2012

Very, Very nice

Jim
vkessler

Community Trekker

Joined:

Dec 23, 2015

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.