From String to Number

[Former Member]

Hello.

I have problem with convert data from String to Number. All data with comma don't convert to number.

It's nvarchar(40) in database.

My function:

shared numberVar sum_dur_op;

shared datetimeVar sum_date_op;

if not isNull({DM_UNDERWATER_DETAIL.cost_code}) and NumericText({DM_UNDERWATER_DETAIL.cost_code}) then (

    sum_dur_op := sum_dur_op + ToNumber({DM_UNDERWATER_DETAIL.cost_code});

    if not isNull({DM_UNDERWATER_OP.start_date}) or year({DM_UNDERWATER_OP.start_date}) <> 0 then

        sum_date_op := DateAdd("h", sum_dur_op, {DM_UNDERWATER_OP.start_date});

);

I have got next results:

sum_dur_op      sum_date_op        

1.68      14.04.2015 0:00:00

1.92      14.04.2015 0:00:00

6      14.04.2015 6:00:00

9.6      14.04.2015 6:00:00

1.92      14.04.2015 6:00:00

I try function Val and have got the same results.

[Former Member · Accepted solution]

Im sure there is a better way to do this but quickly thrown together .....

use this formula for hours

int(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24))

use this formula for minutes

(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24)- int(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24)))*.60

use this formula for seconds

(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24)- int(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24))-(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24)- int(remainder(tonumber({DM_UNDERWATER_DETAIL.cost_code}),24)))*.60) *.60

[Former Member · Accepted solution]

I think It is good idea, but function ToNumber do not work where text with comma or dot.

I use this for solving problem.

part_number := Split ({DM_UNDERWATER_DETAIL.cost_code},".");      //parts of decimal

[Former Member]

in your example above what does 107.52 represent in HH:MM:SS (01:47:52 ?)

[Former Member]

It is 107.52 hour = 4 days 11 hours 31 min 12 sec

[Former Member]

could you show what the string values look like prior to using ToNumber and what you expect it to look like?

[Former Member]

Before (it's hour):

107.52

9.6

24

12

1.2

14.4

12

After:

14.04.2015 0:00:00

14.04.2015 0:00:00

[DellSC]

Try this:

shared numberVar sum_dur_op;

shared datetimeVar sum_date_op;

if not isNull({DM_UNDERWATER_DETAIL.cost_code}) and NumericText({DM_UNDERWATER_DETAIL.cost_code}) then (

    sum_dur_op := sum_dur_op + ToNumber(Replace({DM_UNDERWATER_DETAIL.cost_code}, ',',''));

    if not isNull({DM_UNDERWATER_OP.start_date}) or year({DM_UNDERWATER_OP.start_date}) <> 0 then

        sum_date_op := DateAdd("h", sum_dur_op, {DM_UNDERWATER_OP.start_date});

);

This will take any commas out of the string prior to converting it to a number.

-Dell

[Former Member]

you should also convert to minutes and add "M" instead of H or it will not add the values to the right of any decimal. 1.5 hours would be 90 minutes for example

[Former Member]

Oh, thanks.

I tried this function Replace, but i have taken a mistake with DateAdd.

The DateAdd sum depending on first parametr (ex. "h").

If a number is decimal (ex. 6.12) - the DateAdd can sum only integer part (ex. 6).

And I found a solve:

if not isNull({DM_UNDERWATER_DETAIL.cost_code}) then (

     if NumericText({DM_UNDERWATER_DETAIL.cost_code}) then (

        sum_dur_op := sum_dur_op + ToNumber({DM_UNDERWATER_DETAIL.cost_code});

        if not isNull({DM_UNDERWATER_OP.start_date}) or year({DM_UNDERWATER_OP.start_date}) <> 0 then

            sum_date_op := DateAdd("h", sum_dur_op, {DM_UNDERWATER_OP.start_date});

        )else(

        part_number := Split({DM_UNDERWATER_DETAIL.cost_code},".");      //parts of decimal

        part_number_ost := Fix((((ToNumber(part_number[2])) * 0.6) - Fix((ToNumber(part_number[2])) * 0.6)) * 60);

        sum_dur_op := sum_dur_op + ToNumber(part_number[1]) + ToNumber(part_number[2])/100;

        if not isNull({DM_UNDERWATER_OP.start_date}) or year({DM_UNDERWATER_OP.start_date}) <> 0 then(

            sum_date_op := DateAdd("h", ToNumber(part_number[1]), {DM_UNDERWATER_OP.start_date});      //hour

            sum_date_op := DateAdd("n", Fix((ToNumber(part_number[2])) * 0.6), sum_date_op);     //minute

            sum_date_op := DateAdd("s", part_number_ost, sum_date_op);      //second

)));

But this solve do not add a second.