cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jshaw04
Level III

Date format error for 2020

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

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: Date format error for 2020

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 columnthe datetime column is character data, column2 is a formula column

Column 2 formulaColumn 2 formula

 

Craige

View solution in original post

4 REPLIES 4
Craige_Hales
Super User

Re: Date format error for 2020

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 columnthe datetime column is character data, column2 is a formula column

Column 2 formulaColumn 2 formula

 

Craige
jshaw04
Level III

Re: Date format error for 2020

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.

nath_baillet
Level II

Re: Date format error for 2020

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"
)
Craige_Hales
Super User

Re: Date format error for 2020

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.

Craige