Hi,

I have a routine that takes a data value, and then tries to determine what data type should be used when interfacing with other products (Excel in this case). The process I use is to (a) see if it is a number; then (b) ICONV the data value with various masks to see what is recognised. When I came to test standard QM format masks, I found that these got recognised as TIME values ... which I found surprising.

For example:

ICONV('8R', 'MT')  returns 28800  (8 hours)
ICONV('8L', 'MT') returns 28800
ICONV('8C', 'MT') returns 28800
ICONV('12*R', 'MT') returns 43200 (12 hours)
ICONV('12*R26', 'MT') returns 43226 (12 hours and 26 seconds)

I question whether these are sensible return values for a time conversion. I could understand '8H' being returned as 28800, but not '8R', '8L', or '8C', and certainly not something like '12*R26'. i don't look at any of those values and think - that is a TIME value!

Can someone from Rocket comment on this?

QM 4.0.9 / Windows. Testing on QM 4.0.7 / Windows gives the same answers.

Brian

Hi Brian,

I did some testing with your examples as well as some other combinations and I see what you are describing. It appears that any non-numeric character in the input expression is being treated as a delimiter as the string is evaluated left to right in order to be seen in HH:MM:SS format. If there is only 1 non-numeric, such as 8R5, QM is seeing that as 08:05. If there are 2 such as 8R5*9, QM sees it as 08:05:09. 8R*5 and 8R0*5 would both be seen as 08:00:05.

I ran this question by the dev team and they confirmed that QM is indeed following this logic, and they added additional information.

Case 1: the length of 'input.expr' is 4, all 4characters are digits, and 1^st and 2^nd characters as a double-digit 'h' should be less than 24, and 3^rd and 4^th as a double-digit 'm' should be less than 60, the time value would be (h * 3600) + (m*60);

    X1 = ICONV('1226', 'MT')              returns 44760

Case 2: 1^st character should be a digit, otherwise it is not a valid time;

X1 = ICONV('*1R26', 'MT')            returns nothing

Case 3: The third non-numeric character must be one of the following 'A' or 'P' or 'AM' or 'PM',  otherwise is not a valid time.

    X1 = ICONV('8*1R26', 'MT')          returns 28886

    X2 = ICONV('8*1R26A5', 'MT')     returns nothing

    X3 = ICONV('8*1R26A', 'MT')      returns 28886

Case 4: Minutes and seconds should be less than 60, but hours are not limited.

X1 = ICONV('8*1R60', 'MT')          returns nothing

X1 = ICONV('8*60R26', 'MT')        returns nothing

X1 = ICONV(' 1238*1R26 ', 'MT') returns 4456886

So for any expression passed to the MT conversion, QM will extract a time value if it can. If it can't, the expression will be treated as an invalid time. I hope this clears up the confusion.

Thanks,

Robert