I've been trying to convert a part of a string into a number without success. It started as what seemed to be a really simple calculation:
cast(substring([DataField],1,6), int)
But that didn't work - Cognos threw up this error message:
So...it doesn't like varchar...shouldn't be a problem I thought, I'll convert it into a char(6) first, then convert it into an integer
cast(cast(substring([DataField],1,6),char(6)),int)
But that still didn't work...same error message. Then came the lightbulb moment - this did the job:
substring([DataField,1,6) * 1