Using Char to compare numeric and character values

[#1]

Hi,

Is it possible to compare numeric and character values? 

I want to compare two columns (identical or different values). One column has a numeric date and one has a character date and as I need to compare only parts of it I want to use substring. The below script runs, but it doesn´t get me the correct result as all appears to be different (which is not the case). Am I using Char wrong?

If( Substr( Char( :Name( "Date/Time of Specimen Collection" ) ), 1, 10 ) == Substr( :Name( "Start Date/Time of Visit" ), 1, 10 ),
"identical",
"different"
)

I know I can change the format of "Date/Time of Specimen Collection" but I prefer not to as it will complicate other parts of my script.

Thanks in advance

[#2]

To understand what's happening, make a temporary column with

Char( :Name( "Date/Time of Specimen Collection" ) )

as the formula. You'll probably need to use the format(...) function (instead of char(...)) to specify the exact date-time format you need to get all the values and separator characters correct. You can see the available format names when you use the column properties dialog and look at the format...date section.

[#3]

I see your point. I cannot seem to get it right using format though.

Is there anyway of doing this without creating extra columns?

[#4]

Yes. What is the format of the character date?

[#5]

In the attached table snip I´m trying to compare the date part of "Date/Time of Specimen Collection" column (numeric, yyy-mm-ddThh:mm:ss) with the "Start Date/Time of Visit" column (character, nominal)

Is there a function that allows jmp to see the numerical values as character (formatted as a date of course) so that I can use substring for the date part of it? 

[#6]

Using the format() function; the numeric date is in year-month-day for 7 February; the character format is in day/month/year.

[#7]

You might need this format:

the T format is an ISO standard.

[#8 · Accepted solution]

Or even this:

You don't need substr for the yyy-mm-dd format; it already returns just the 10 characters you need.

[#9 · Accepted solution]

Thanks a lot - I´ll give that a shot :)