topic Re: How convert string date into datetime value in Discussions

How to convert string date into datetime value

geof — Tue, 22 Aug 2017 20:02:13 GMT

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

XanGregg — Fri, 20 Sep 2013 20:03:54 GMT

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

Re: How convert string date into datetime value

geof — Tue, 24 Sep 2013 12:10:07 GMT

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

Re: How convert string date into datetime value

michaelhaslam_p — Thu, 21 Mar 2019 17:22:57 GMT

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

geof — Thu, 21 Mar 2019 17:23:34 GMT

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 — Thu, 26 Sep 2013 13:44:48 GMT

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