Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
tnodd
Level II

Bug in Creating a Column for Date Splitting

Hello,

The problem is very simple and I am able to work it out, but somehow theere seems to be a bug in the output. So wondering if there is an error in my formula?

 

I have a JMP table with a time Col in the format as (mm/dd/yyyy h:m:s AM/PM)

 

dt << New Column( "DATE_FLAG",
    Character,
    Nominal,
    Formula( If( Abbrev Date( :Name( "TIME_COL@ABC" ) ) <= Abbrev Date( 22Jul2020 ), "before", "after" ) )
);

 

Now finally in my updated table I am getting the DATE_FLAG Column and before/after values. But some of the values/rows) which should be before say the dates are before 22nd July 2020 are still labelled as after? The rest of the rows are labelled as expected.

 

So what is wrong? Do I have to specify the date time format? If so how can I ?

 

I think I figured out the problem, but need some help with that:

 

The Abbrev Date comparison is comparing the two Character Date strings and that's why when 07/04/2020 10:12:34 AM is converted to 07/4/2020 when compared with 07/22/2020 is giving after instead of before. 

 

How can I fix this?

 

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Bug in Creating a Column for Date Splitting

There's no need to do any conversion to anything else to compare date values.

 

Just use a date constant in your comparison by specifying the date in ddMonYYYY format:

 

dt << New Column( "DATE_FLAG",
	Character,
	Nominal,
	Formula( If( :Name( "TIME_COL@ABC" ) <= 22Jul2020, "before", "after" ) )
);

Read more about date values: 

Using dates, times, datetimes and durations in JMP 

-Jeff

View solution in original post

3 REPLIES 3
Highlighted
tnodd
Level II

Re: Bug in Creating a Column for Date Splitting

I think the issue is fixed by replacing Abbrev Date with Short Date. Abbrev Date converts it to a character, but ShortDate keeps it in the Date Time Format, which makes the correct comparison.
Highlighted
tnodd
Level II

Re: Bug in Creating a Column for Date Splitting

Wow! Thanks. Thats more simple!!!

Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Bug in Creating a Column for Date Splitting

There's no need to do any conversion to anything else to compare date values.

 

Just use a date constant in your comparison by specifying the date in ddMonYYYY format:

 

dt << New Column( "DATE_FLAG",
	Character,
	Nominal,
	Formula( If( :Name( "TIME_COL@ABC" ) <= 22Jul2020, "before", "after" ) )
);

Read more about date values: 

Using dates, times, datetimes and durations in JMP 

-Jeff

View solution in original post