trim function for a number?

[shannonlp]

Hi,

I need to create a formula field that will take a number and format it from 12 characters to 8. An example is 19,911101.00 needs to be converted to 19911101. You can probably tell that this is a date. I’m not aware of a trim-like function for numbers so I’m a little stuck.

I have no influence/control over what our dba will do so which means I am only able to work with the data in its current format. I’m thinking that the quickest way to complete this report is to create a formula, and then create a parameter. I'm on Crystal XI working in an Oracle 9i environment.

Thanks for any help.

 

[satinsilhouette]

trim is a string function. What you could do is convert the date field to a string, trim it and then convert it back to a number field or date field.

tonumber(trim(totext({datefield})))

 

[lbass]

If this field has a datatype of number, as you suggest, then all you need to do is right click->format field->number and choose -1123 for a format.

-LB

 

[shannonlp]

Hi,

Thanks for the responses. I need to do more than just format that way the number is displayed on the report. I am trying to create a parameter on the number (which is actually a date). I have no trouble creating the parameter (static, type is number), but then when running the report, I’m not seeing results when I should be. I ran same query in sql plus and got results. I am also noticing when I refresh the report, the value of the parameter is stored in the original format.

In the meantime, I’m having our dba check the structure that was built and make sure this is correct – this relates to a data warehouse project involving multiple systems which is causing all kinds of data integrity issues.

Thanks again.

 

[TechGuy9707]

I have a great understanding of what you are trying to do. Take your NumericDate (19,911101.00) and write a function NumericDateText with the command toText(NumericDate) that converts the number to text and then use something like this

select left({CLAIM.DATE_COMPLETED},1)
case '0' : cdate (2009,1,1)
case '1' to '2' : CDate (mid({CLAIM.DATE_COMPLETED},1,4)&','& mid({CLAIM.DATE_COMPLETED},5,2)&','& mid({CLAIM.DATE_COMPLETED},7,2))
default: cdate(1999,12,31)

The Select is to trap invalid dates like 34129034.00 and sets null dates to 1/1/2009