7416_en-US

liliconcom

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

Re: How convert string date into datetime value<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

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

 

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

Re: How convert string date into datetime value

Hi

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

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