Date format error for 2020

[#1]

I have a set of data where I have a couple different formats for dates and I am creating a new column to convert the dates to one format.  The dates are in d/m/y h:m:s format.  When I run the script all of the 2019 dates translate fine, but the 2020 dates get their month day reversed.  If I go into column information and change the settings manually it works fine.  What can I do within JSL to retain the correct format?

DateTime

5/1/2020 1:10
5/1/2020 1:10
5/1/2020 1:10
30-12-2019 14:01:18
30-12-2019 14:01:18
30-12-2019 14:01:18

New Column("DateTime2",numeric,"Continuous",format("d/m/y h:m:s",22,0),input format("d/m/y h:m:s",0));

for each row( :DateTime2 = num(:DateTime));

DateTime DateTime2
5/1/2020 1:10 01/05/2020 1:10:00 AM
5/1/2020 1:10 01/05/2020 1:10:00 AM
5/1/2020 1:10 01/05/2020 1:10:00 AM
30-12-2019 14:01:18 30/12/2019 2:01:18 PM
30-12-2019 14:01:18 30/12/2019 2:01:18 PM
30-12-2019 14:01:18 30/12/2019 2:01:18 PM

[#2 · Accepted solution]

Your input date is character data, and you are using the num() function to guess the date's format. Use the informat function and specify the format. Some of your data has seconds and some doesn't. You can use regex to keep everything up to where the seconds might be. \d+ matches one or more digits and \D matches non-digit.

Informat( Regex( :datetime, "\d+\D\d+\D\d+\D\d+\D\d+" ), "d/m/y h:m" )

JMP guesses m/d/y for ambiguous dates; the example dates you have for 2019 are not ambiguous.

the datetime column is character data, column2 is a formula column

Column 2 formula

[#3]

Thank you Craige.  You are correct, the dates were not ambiguous due to end of year inputs for 2019.  Good catch.

I wouldn't have thought about the Regex, so appreciate your insights there as well.

[#4]

Merci pour la formule.

J'ai eu besoin des secondes, j'ai donc mis

Informat(
	Regex( :Name( "Time-court" ), "\d+\D\d+\D\d+\D\d+\D\d+\D\d+" ),
	"d/m/y h:m:s:sc"
)

[#5]

Great, and you're welcome! Glad that is working for you.

The regex(...) might not be needed if your data is all the same format. In the original question, some values had seconds and some did not. By keeping only the hours and minutes, the hh:mm format would work for all of them.

The important part is to specify the proper format to read the dates.