converting a date from a string into a JMP date : AM/PM vs 24hr time problems

[#1]

Consider the following scenario :

MyDateStr = "2/19/2018 10:04:28 PM";
informat(MyDateStr, "m/d/y h:m:s");

This returns a correct date/time value ( 19Feb2018:22:04:28 ) in the JMP log, as long as the date/time format on my computer is set to hh:mm:ss tt  ( ie 12 hr time )

If I have the format on my computer set to 24hr time ( HH:mm:ss ) and I run the above code, JMP gives me a missing value. It fails to correctly interpret the date. If I drop the "PM" off the end it will work.

How do I get JMP to correctly parse date/time strings specified in 12 hr time when running on a computer set to 24 hr time ?

[#2]

Hi,

This is a "hail Mary" type of reply. What do you get if you just use Informat( MyDateStr ) or equivalently,

Parse Date( MyDateStr ). 

Also, in the book Using JMP  chapter The Column Info Window it states that "Locale Date Varies based on local OS setting."  And the JSL Syntax Reference  shows 

     Format( x, "Currency", "EUR", 20, <<Use Locale(0)); // ignores computer locale

InFormat() and Parse Date() show a 3rd argument  <<Use Locale(0).

So my "hail mary suggestions are:

• use Informat( MyDateStr ) alone
• use Informat( MyDateStr, "m/d/y h:m:s", << Use Locale(0) );
• create a column from the string

See the script.  It shows the power of Informat() even propery converting 30/12/.... But conversion in place (at least our previous methods) no longer worked.  I hope you find something that works.

//------#6: Create a data table with a Character column of different date time formats
dtimeList={};
InsertInto( dtimeList, "JUN162010 2:59:13");
InsertInto( dtimeList, Format(AsDate(Today()), "yyyy-mm-ddThh:mm:ss"));
InsertInto( dtimeList,Format(AsDate(Today() - 3*34*60), "yyyy-mm-ddThh:mm"));
InsertInto( dtimeList,Format(AsDate(Today() + 12*34*60), "yyyy-mm-ddThh:mm"));
InsertInto( dtimeList, "12/01/2010");
InsertInto( dtimeList, "01/12/2010");
InsertInto( dtimeList, "30/12/2010 1:1:1");

test3_dt = New Table ("Test Datetime", new Column("dummy", character));
test3_dt << add rows( nitems(dtimelist));
test3_dt:dummy << set values (dtimeList);
//----run to here---------------------------------------------------------

//----Look at the data table...create a new column using Parse Date function
convCol = test3_dt << New Column( "Parsed dummy", Numeric, Continuous );
convCol << set each value (Informat(:dummy) );
convCol << Format("monddyyyy h:m:s");
wait(2);
convCol << Format("m/d/y h:m:s");


//---The code below worked in JMP 8 and 9 and 10 and maybe 11. It does not work in JMP 12, 13, 14
//Also, even though the column has mixed formats, by converting to numeric, 
//continuous and specifying a datetime format, JMP automatically parses the dates
//:dummy << {data type( "Numeric"), Modeling Type("Continuous") };
//:dummy << Format("m/d/y h:m:s" );

[#3 · Accepted solution]

I concluded that it just can't be done in a 1 liner :

 << use Locale(0) has no effect on my machine, it still will not interpret a timestamp that includes AM/PM unless the computer is set to 12 hour time.

there's a similar discussion I found  here.

My conclusion was the only way I can reliably do it is like this:

AMPM = substr(TimeStampStr,-2,2);

TimeStampStr = substitute(TimeStampStr," AM",""," PM", "");

Match(AMPM,

"AM", try(:TimeStamp = informat(TimeStampStr, "m/d/y h:m:s"));

,

"PM", try(:TimeStamp = informat(TimeStampStr, "m/d/y h:m:s") + InHours(12))

);

Which is a whole lot of code to do something rather trivial but it does work