Best way to convert UTC time format to numeric

[#1]

Hi All,

 (JMP 17 & JMP 18EA)

  I have some XML data with a column that contains a time stamp, but the data format is not very compatible for what I'd like to do with it in JMP.

  The data is in the following UTC format yyyy-mm-ddThh:mm:ss.SSSZ, where the usual meanings are applied to the abbreviations. Here, there is actually a "T" after dd, and a Z after ".SSS", and the .SSS stands for milliseconds, there are no spaces. Unfortunately, JMP doesn't appear to have a built-in date format that can handle this well, even though it does have a similar format, but without the milliseconds and Z; so JMP can handle time in the format yyyy-mm-ddThh:mm:ss.

 The only way I can get JMP to parse the UTC date/time is by using the Substr() command in a column formula, remove the column formula, and then change the data type and format. This won't be too difficult to script, but just wondering if someone else has a more efficient way of handling date/time data in this unusual format.

  Or, does anyone know if JMP is considering to include this UTC time format to make conversion easier?

Thanks!,

DS

[#2 · Accepted solution]

You can get quite far by using Format Pattern with <YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>Z

Names Default To Here(1);

date_str = "2022-12-23T14:54:50.123Z";

x = Informat(date_str,
	"Format Pattern",
	"<YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>Z"
);

show(x, Second(x));

(might have to do some rounding)

[#3]

Hi @jthi ,

  Excellent, that is much more efficient at doing the conversion, thank you!

Thanks!,

DS