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

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

tsl — Fri, 06 Apr 2018 22:57:19 GMT

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 ?

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

gzmorgan0 — Fri, 13 Apr 2018 00:28:38 GMT

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

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

tsl — Fri, 13 Apr 2018 16:37:44 GMT

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

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

JBailey_API — Tue, 31 Dec 2024 00:05:34 GMT

Even the

<< Use Locale(0)

worked for me. I found that nowhere in the documentation. Man, date/time conversions are so difficult!