Character to DateTime Conversion Help Needed

[#1]

Hi 

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

[#2]

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.

[#3]

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

[#4]

thx txnelson for your support

[#5]

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>

[#6]

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:

[#7]

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. 

[#8]

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.

[#9]

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

thx again 

[#10 · Accepted solution]

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