Post Author: LaVerne
CA Forum: Formula
Good Morning:
I am attempting to convert a database field from a numeric field to date. The current format of the field looks like this:
20,070,221.00 and states that it is numeric.
This is the syntax recommended by our software vendor to convert the field to date format:
stringVar y := Left(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5);
stringVar m := Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),6, 3);
stringVar d := Right(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5);
If {DS_ENCOUNTER.ADMIT_DATE} > 0
THEN
DATE (ToNumber (y), ToNumber (m), ToNumber (d))
I checked the formula syntax and it says there isn't an error however when I run the report I get the following error:
"The String is non-numeric"
Thanks for your help!!
Post Author: SKodidine
CA Forum: Formula
Could it be that {DS_ENCOUNTER.ADMIT_DATE) may not have two digits for the month and/or day. For example, Feb is 2 rather than 02?
Try this and see if it works:
If {DS_ENCOUNTER.ADMIT_DATE} > 0 and
not isnull({DS_ENCOUNTER.ADMIT_DATE}) and
length(totext({DS_ENCOUNTER.ADMIT_DATE})) = 13
THEN
(
stringVar y := Left(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5);
stringVar m := Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),6, 3);
stringVar d := Right(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5);
DATE (ToNumber (y), ToNumber (m), ToNumber (d)) ;
)
else
date(0,0,0);
Post Author: kcheeb
CA Forum: Formula
Looks to me like the commas and periods are messing you up. If the ToText method formats the ADMIT_DATE as indicated. Can you confirm that they get extracted by the Lef, Mid & Right functions?
Left(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5); = 20,07
Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),6, 3); = 0,2
Right(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5); = 21.00
Post Author: V361
CA Forum: Formula
I used part of Skodines formula as follows.
stringVar y := Left(ToText("20,070,221.00"), 5);
stringVar m := Mid(ToText("20,070,221.00"),6, 3);
stringVar d := Right(ToText("20,070,221.00"), 5);
DATE (ToNumber (y), ToNumber (m), ToNumber (d)) ;
it works just fine for me. my out put is 02/21/2007 and
CR is treating it as a date.
Post Author: kcheeb
CA Forum: Formula
I think the question is now answered. When you convert the string, 20,07 to a number, the conversion program sees it as non-numeric and throws an exception.
You may have to strip out the commas and periods before you pass the values for conversion. Or find a way to not format the number with commas and periods in the first place.
For the latter, the Left function would be the first 4 characters, the mid would be 5,2 and the right would be 2, unless there were two decimals, in which case I would change it to a mid of 7,2
Post Author: kcheeb
CA Forum: Formula
To do it in one step per data element :
Left(ToText({DS_ENCOUNTER.ADMIT_DATE}),2) + Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),4,2)
Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),6,1) + Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),8,1)
Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),9,2)
This should work.
Post Author: yangster
CA Forum: Formula
easier way would be to replace the commas with blanksnumbervar y := tonumber(replace(Left(ToText({DS_ENCOUNTER.ADMIT_DATE}), 5), ",", ""));
numbervar m := tonumber(replace(Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}),6, 3), ",", ""));
numbervar d := tonumber(Mid(ToText({DS_ENCOUNTER.ADMIT_DATE}), 9, 2));
DATE (y, m, d) ;
Add a comment