Subscribe Bookmark RSS Feed

How convert string date into datetime value

geof

Community Trekker

Joined:

May 29, 2013


Hi all,

Using JMP10 I import csv data with string date like yyyy-mm-dd-hh:mm:ss:SSSSS , example 2013-09-01-14:02:33:759000. For jmp it is CHARACTER

How to convert this string date format into a "real" datetime (NUMERIC) like yyyy-mm-dd hh:mm:ss  or yyyy/mm/dd h:m:s ?

Thanks

geof

5 REPLIES
XanGregg

Staff

Joined:

Jun 23, 2011

The easiest way is probably to convert the date into something JMP understands like ISO-8601: yyyy-mm-ddThh:mm:ss.sss. This formula should do it (assuming the source is in a column called "d"):

     Parse Date( Substr( :d, 1, 10 ) || "T" || Substr( :d, 12, 8 ) || "." || Substr( :d, 21 ); );

JSL for interactive tinkering:

    s = "2013-09-01-14:02:33:759000";
    t = Substr( s, 1, 10 ) || "T" || Substr( s, 12, 8 ) || "." || Substr( s, 21 );
    Parse Date( t );
geof

Community Trekker

Joined:

May 29, 2013

Hi

When I run the script, no error, but no change in the data ! ?

michaelhaslam_p

Community Trekker

Joined:

Sep 15, 2013

geof:

Try adding a new column to your table that is type date/time and put your converted values there using a formula.  Here is an example:

New Table( "Time Example",

Add Rows( 1 ),

New Column( "Column 2",

  Character,

  Nominal,

  Set Values( {"2013-09-01-14:02:33:759000"} )

),

New Column( "Column 3",

  Numeric,

  Continuous,

  Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),

  Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),

  Formula(

   Parse Date(

    Substr( :Column 2, 1, 10 ) || "T" || Substr( :Column 2, 12, 8 ) ||

    "." || Substr( :Column 2, 21 )

   )

  )

)

)

Michael Haslam

geof

Community Trekker

Joined:

May 29, 2013

Hi Michael,

I ran your script and it works fine

I ran the script below with "Time" column where all the data are.

But I got "." for all data :-(

Current DataTable();


New Column( "Column 3",

  Numeric,

  Continuous,

  Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),

  Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),

  Formula(

   Parse Date(

    Substr( :Time, 1, 10 ) || "T" || Substr( :Time, 12, 8 ) ||

    "." || Substr( :Time, 21 )

   )

  )

)

geof

Community Trekker

Joined:

May 29, 2013

I fixed the issue.

Your string format was 2013-09-01-14:02:33:759000

Mine is 2013-09-01-14.02.33.759000

Thanks