Handling different time formats with JMP with different regional settings

[#1]

Let's start with the purpose of this post:

Have you had issues with dates and times when users have different regional settings and how have you handled them?

From time to time I have faced issues with different time formats (and date) when doing scripting and sharing those scripts (have been working with users having different locale and JMP settings recently). This issue is usually related to time format, as JMP doesn't provide "general" format for time (for date I can use Locale Date).

Before JMP16 I have done my own code to check if user is using : or . as time separator and then built Locale Date Time format based on that:

timeSeparator = Left(Regex(Format(Today() ,"h:m:s"), "\d", "", GLOBALREPLACE),1);
customFormat = "Locale Date Time h"||timeSeparator||"m"||timeSeparator||"s";

JMP16 helps with this with Format Pattern but it isn't the most simple one to use when doing formatting (user has to know to use <::> for time separator):

Format("Format Pattern", "<YYYY><-><MM><-><DD> <hh24><::><mm><::><ss>");

Depending what I'm doing I have different solutions, if I just want to have date and time in textbox I usually just use AsDate(), but when I want to build specific reports or provide GUI with Number Edit Box which has calendar box option I cannot use that.

Below are couple of examples on the issue and solutions I'm currently using depending on JMP version:

View more...

Names Default To Here(1);
Clear Log();
Print("New window");
nw = New Window("", Lineup Box(N Col(2),
	Text Box("Format Pattern <DD><-><MM><-><YYYY> <hh24><::><mm><::><ss>", << Set Wrap(500)),
	cal1 = Number Edit Box(Today()),
	Text Box("Locale Date Time h:m:s", << Set Wrap(500)),
	cal2 = Number Edit Box(Today()),
	Text Box("Locale Date Time h.m.s", << Set Wrap(500)),
	cal3 = Number Edit Box(Today()),
	Text Box("yyyy-mm-ddThh:mm:ss", << Set Wrap(500)), //ISO format?
	cal4 = Number Edit Box(Today()),
	Text Box("Built locale date format", << Set Wrap(500)),
	cal5 = Number Edit Box(Today()),
	Text Box("No format", << Set Wrap(500)),
	cal6 = Number Edit Box(Today())
));

cal1 << Set Format(Format("Format Pattern", "<DD><-><MM><-><YYYY> <hh24><::><mm><::><ss>"));
cal2 << Set Format(Format("Locale Date Time h:m:s"));
cal3 << Set Format(Format("Locale Date Time h.m.s"));
cal4 << Set Format(Format("yyyy-mm-ddThh:mm:ss"));

timeSeparator = Left(Regex(Format(Today() ,"h:m:s"), "\d", "", GLOBALREPLACE),1);
customFormat = "Locale Date Time h"||timeSeparator||"m"||timeSeparator||"s";
cal5 << Set Format(customFormat);

Print("Show");
Show(Format(Today(),"Format Pattern", "<DD><-><MM><-><YYYY> <hh24><::><mm><::><ss>"));
Show(Format(Today(),"Locale Date Time h:m:s"));
Show(Format(Today()(),"Locale Date Time h.m.s"));
Show(Format(Today(),"yyyy-mm-ddThh:mm:ss"));
Show(Format(Today(),customFormat));
Show(Today());

Print("Data table");
dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("FormatPattern", Numeric, "Continuous", Format("Best", 12), << Set Each Value(Today())),
	New Column("LocaleDateTime:", Numeric, "Continuous", Format("Best", 12), << Set Each Value(Today())),
	New Column("LocaleDateTime.", Numeric, "Continuous", Format("Best", 12), << Set Each Value(Today())),
	New Column("ISO?", Numeric, "Continuous", Format("Best", 12), << Set Each Value(Today())),
	New Column("Custom", Numeric, "Continuous", Format("Best", 12), << Set Each Value(Today())),
	New Column("NoFormat", Numeric, "Continuous", Format("Best", 12), << Set Each Value(Today()))
);

Column(dt, "FormatPattern") << Format("Format Pattern", "<DD><-><MM><-><YYYY> <hh24><::><mm><::><ss>");
Column(dt, "LocaleDateTime:") << Format("Locale Date Time h:m:s");
Column(dt, "LocaleDateTime.") << Format("Locale Date Time h.m.s");
Column(dt, "ISO?") << Format("yyyy-mm-ddThh:mm:ss");
Column(dt, "Custom") << Format(customFormat);
Show(Today());

My results using JMP16:

View more...

I have created JMP support feature request about having General Time format option (Locale Date Time, or ISO-format) in JMP, but the support person wasn't too hopeful that it would be implemented (maybe if I create a Wish List item and it gets Kudos, it might get a better chance).

And repeat of the question:

Have you had these issues with dates and times and how have you handled them when users have different regional settings?

Edit:

Format settings from my work laptop using JMP16 Pro:

View more...

From my other PC with very weird date formats using JMP15:

View more...

[#2 · Accepted solution]

This behaviour will be changed at latest in JMP17. Below is an example how it should work in JMP17:

Format(Today(), "Locale Date Time h:m:s"); = "12/07/2021 16.07.20"
Format(Today(), "Locale Date Time h.m.s"); = "12/07/2021 16.07.20"

Until JMP17 I will be using either Format Pattern (JMP16) or my Locale Date building methods