How to convert string date into datetime value

[#1]

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

[#2]

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

[#3]

Hi

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

[#4 · Accepted solution]

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

[#5]

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

[#6]

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