BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
KST-CPT
Community Trekker

Convert Character Date Time to Date Time

Having difficulty converting to a numeric date time. 

JMP log error: Column "Date-Time" changed to type Character to match formula.

 

Date::Time original format:

01:28:2018::02:14:08:099

 

What I am trying to do (after many attempts from various posts):

dt<< new column ("Date-Time", numeric, formula(
rawdate = substitute(trim(substr(:TimeStamp,1,10)),":","/");
rawtime = (trim(substr(:TimeStamp,13,12)));
datetme = rawdate || " " || rawtime;
//informat(datetime);
//as date(datetme);
)
) ;

 

Informat() and as date() give me "." result

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Convert Character Date Time to Date Time

Here is the conversion formula I came up with

Date MDY(
	Num( Substr( :TimeStamp, 1, 2 ) ),
	Num( Substr( :TimeStamp, 4, 2 ) ),
	Num( Substr( :TimeStamp, 7, 4 ) )
) 
+ 3600 * Num( Substr( :TimeStamp, 13, 2 ) ) 
+ 60 * Num( Substr( :TimeStamp, 16, 2 ) )
+Num( Substr( :TimeStamp, 19, 2 ) )

and I am specifying a format of     m/d/y h:m:s    for the new column

 

Jim
5 REPLIES 5
txnelson
Super User

Re: Convert Character Date Time to Date Time

Here is the conversion formula I came up with

Date MDY(
	Num( Substr( :TimeStamp, 1, 2 ) ),
	Num( Substr( :TimeStamp, 4, 2 ) ),
	Num( Substr( :TimeStamp, 7, 4 ) )
) 
+ 3600 * Num( Substr( :TimeStamp, 13, 2 ) ) 
+ 60 * Num( Substr( :TimeStamp, 16, 2 ) )
+Num( Substr( :TimeStamp, 19, 2 ) )

and I am specifying a format of     m/d/y h:m:s    for the new column

 

Jim
KST-CPT
Community Trekker

Re: Convert Character Date Time to Date Time

That worked flawlessly. I added to the column properties in the script: continuous, format("m/d/y h:m:s").

 

I am not a software engineer or related, just trying to hack my way through this. I guess I don't understand how date and time work in JMP.  The time stamp does not need to be in "MM/DD/YYYY hh:mm:ss:msc" format? From what I found in other posts, I thought that was the correct format when converting from character to date/time.  I also see that you grab each number alone and then change the type, I am assuming that JMP could not handle the character inputs mixed in with the numerics?

 

Thank you very much again for your help.  Much appreciated.

0 Kudos
Highlighted
txnelson
Super User

Re: Convert Character Date Time to Date Time

Can you please supply a sample of your character string date time column. I am assuming that all you have to do is to strip off the seconds and milliseconds, convert that string to a numeric, and then add that to calculation of the JMP Date/Time value.
Jim
joelahn
Community Member

Re: Convert Character Date Time to Date Time

a quick question here;
If we want to keep the last three digits of decimals for sec (miliseconds) in time format, how do we keep it in formula?
Thanks much from newbie.
0 Kudos
Jeff_Perkinson
Community Manager Community Manager

Re: Convert Character Date Time to Date Time

As @txnelson says, we could use some more detail and an example but it may be that you just need to specify the number of decimals to display in the format of your column.

 

JMPScreenSnapz006.png

-Jeff
0 Kudos