Changing character column to date colum

vkessler

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!

txnelson

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