cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
geof
Level III

How to convert string date into datetime value


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

1 ACCEPTED SOLUTION

Accepted Solutions
michaelhaslam_p
Level III

Re: How convert string date into datetime value

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

View solution in original post

5 REPLIES 5
XanGregg
Staff

Re: How convert string date into datetime value

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
Level III

Re: How convert string date into datetime value

Hi

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

michaelhaslam_p
Level III

Re: How convert string date into datetime value

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
Level III

Re: How convert string date into datetime value

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
Level III

Re: How convert string date into datetime value

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