Skip to Content
author's profile photo Former Member
Former Member

Conversion Error Numeric to Date

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!!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

8 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 07:47 PM

    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);

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 07:57 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:21 PM

    Post Author: LaVerne

    CA Forum: Formula

    I have confirmed that the date elements are extracting correctly with the Left, Mid and Right functions.

    Left = 20,07

    Mid = 0,2

    Right = 21.00

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:29 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:31 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 06:39 PM

    Post Author: LaVerne

    CA Forum: Formula

    How would I go about striping out the comma and decimals? Your help is appreciated!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 07:07 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 09:30 PM

    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
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.