topic Re: Character to DateTime Conversion Help Needed in Discussions

Character to DateTime Conversion Help Needed

altug_bayram — Thu, 08 Jun 2023 16:32:45 GMT

I am attaching a sample single row table.

Date1 column is character

e.g. 03-Mar-22 12.13 PM

ie. DD-MMM-YY HH.MM am/pm

Date2 has a datetime format

I need a formula for especially Date1 to convert it into DateTime w/ desired format of

MM/DD/YYYY HH:MM:SS am/pm

(SS, seconds would be automatically zero )

My goal is to update a table based on a second one (or join) once I can 2 comparable datetime columns

note: I tried some attempts, none worked so far. Appreciate the help.

thanks

Re: Character to DateTime Conversion Help Needed

txnelson — Thu, 02 Mar 2023 08:29:27 GMT

Here is one format that appears to work

monList = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
Informat(
	Char( Contains( monList, Word( 2, :Date1.Char, "-" ) ) ) || "/" || Word( 1, :Date1.Char, "-" ) || "/" || Word( 3, :Date1.Char, "-" ),
	"m/d/y h:m"
);

The above JSL works only if the period in the time area of the data is changed to a semicolon.

My suggestion is to use @brady_brady solution below.

Re: Character to DateTime Conversion Help Needed

jthi — Wed, 01 Mar 2023 06:32:05 GMT

If you have JMP16+ you might be able to do this with Format Pattern

Other pattern is something like <DD>-<MMM>-<YY> <hh>.<mm> <AMPM> and other <MM>/<DD>/<YYYY> <hh>:<mm>:<ss> <AMPM>

Re: Character to DateTime Conversion Help Needed

altug_bayram — Wed, 01 Mar 2023 16:18:36 GMT

Jim,

Where do I put this ?

I tried it as a formula, did not work

I tried entering this as "Set Format Pattern" (like jthi's suggestionb) and cannot enter more than one line.

I use JMP 16

thanks

Re: Character to DateTime Conversion Help Needed

altug_bayram — Wed, 01 Mar 2023 17:24:02 GMT

I have JMP 16 and It did not work. Below are my steps. Would appreciate if you can point to a mistake.

First I put a formula (that was forgetten earlier) into

Date1.DateTimeFinalFormat = Date1.Char

Set Date1.DateTimeFinalFormat date type/modeling to Numeric and Continuous

Set format to Format Pattern

Uncheck "Use Output Format"

Set Format Pattern

<MM>/<DD>/<YYYY> <hh>:<mm>:<ss> <AMPM>

On Input side, Set Format Pattern

<DD>-<MMM>-<YY> <hh>.<mm> <AMPM>

to which an error is issued:

Re: Character to DateTime Conversion Help Needed

altug_bayram — Wed, 01 Mar 2023 17:26:38 GMT

I also changed HH to hh and AMPM to ampm on input side..

At one point, it accepts the format and then returns Date1.DateTime.FinalFormat column back to Character.

Re: Character to DateTime Conversion Help Needed

brady_brady — Thu, 02 Mar 2023 00:35:42 GMT

Hi Altug,

I hope you are well--it's been a long time!

I am assuming you are doing this by scripting. JMP can handle many date and time formats natively. When this is the case, the Informat ( ) function is great, because it converts a string to a time.

Your date is pretty close to a native format. 03-Mar-22 12.13 PM is not a native format, but 03/Mar/22 12:13 PM is !

So, all you need to change is your dashes to slashes ( "-" becomes "/" ), and your period to a colon ( "." becomes ":" ).

The Substitute ( ) command can do this for you. Once this is done, you can wrap that in an Informat ( ) to get a numeric date. All that remains is to apply your desired display format, which is a Locale format.

So the process is to write a new column that:

a) has a formula that wraps an Informat ( ) around a Substitute ( ), and

b) formats the output to look like you want, while still retaining the numeric date underneath.

Let us know how this goes,

Brady

Names Default To Here( 1 );

dt = Current Data Table();

dt << New Column( "FixedDate",
	formula( Informat( 
		Substitute( :"date1.char"n, 
		"-", "/", 
		".", ":" 	
		)
	) ),
	<<Format( "Locale Date Time h:m:s", 23, 0 )
);

Re: Character to DateTime Conversion Help Needed

jthi — Thu, 02 Mar 2023 08:16:23 GMT

I have had my fair share of issues with JMP and how it handles dates (and especially times) when users have different settings on PC. JMP16+ have made it a bit better but they can still be very annoying to debug if users have different local settings.

"Easiest" (and most robust) option is always (in my opinion) to build the datetime from scratch based on the situation. Get different parts of the date and time and then for example use Date MDY + calculation of time to get JMP datetime. After you have JMP's datetime you can then format it as needed.

You could also trial and error and try to get the correct pattern first in script editor:

Names Default To Here(1);

datetime_str = "03-Mar-22 12.13 PM";
show(datetime_str);

datetime = In Format(
	datetime_str,
	"Format Pattern",
	"<DD>-<MMM>-<YY> <hh>.<mm> <AMPM>"
);

show(datetime);

datestr = Format(datetime, "Format Pattern", "<MM>/<DD>/<YYYY> <hh>:<mm>:<ss> <AMPM>");
show(datestr);

// If I couldn't get Format Pattern fairly quickly, I would just give up and write my own
// parser. Something like this might work
datetime_str = "03-Mar-22 12.13 PM";
months = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};

date_str = Word(1, datetime_str);
time_str = Word(2, datetime_str);
ampm_str = Word(3, datetime_str);

day_str = Word(1, date_str, "-");
month_str = Word(2, date_str, "-");
year_str = Word(3, date_str, "-");

day_num = Num(day_str);
month_num = Contains(months, month_str);
year_num = 2000 + Num(year_str);

hour_str = Word(1, time_str, ".");
minute_str = Word(2, time_str, ".");

minute_num= Num(minute_str);

hour_num = Num(hour_str);
If(ampm_str == "AM",
	if(hour_num == 12,
		hour_num -= 12;
	);
, ampm_str == "AM",
	if(hour_num == 12,
		hour_num = 12
	,
		hour_num += 12
	)
);

date_datetime = Date DMY(day_num, month_num, year_num);
time_datetime = In Hours(hour_num) + In Minutes(minute_num);

final_datetime = date_datetime + time_datetime;
As Date(final_datetime);

If I have to play around with dates and times I will always try to go for ISO-format YYYY-MM-DD hh24:mm:ss as it at least has some hope of working and you know where the month is without being lucky.

Re: Character to DateTime Conversion Help Needed

altug_bayram — Thu, 02 Mar 2023 21:48:52 GMT

Brady,

It's been long time and I hope you are doing well.

Your technical solution certainly does well, as it did work great. In the end, it was so simple.

thanks again for your support.

Re: Character to DateTime Conversion Help Needed

altug_bayram — Thu, 02 Mar 2023 21:50:42 GMT

thanks jthi for your support. Will give this one a try as well - at least to learn better.

thx again

Re: Character to DateTime Conversion Help Needed

altug_bayram — Thu, 02 Mar 2023 21:51:24 GMT

thx txnelson for your support