convert a oracle number field to a time field

[cm1234]

Hello out there

Im running CR 9,0 and the data in this question comes from oracle 9,0
Here comes my question:
How can i convert a oracle numberfield to a timefield in CR
Data looks like this: 10,51
00,39
because its stored as
number the summary would be 10,90
and i need it to be 11:30 as time.

Hope this makes some sence
and somebody can help here???
best regards
cm1234

 

[bdreed35]

Here is a way to do it:

stringvar array strTime := split({table.field},",");
numbervar hours := val(strTime[1]) + int(val(strTime[2]) / 60);
numbervar mins := remainder(val(strTime[2]),60);

time(hours,mins,0);

Here is another way to do it in just one line:

time(val(left{table.field},2)) + int(val(right({table.field},2)) / 60),remainder(val(right({table.field},2)),60),0)

~Brian

 

[cm1234]

Hi Bdreed 35
Thanks for your quick reply to my issue
i have tried to use the code you suggested
but in code 1 i get the message "a string is required here"
when i copy my field in the place off {table.field}.

and the last code gives the message
"the )is missing" and i have tried everything to solve that
but it dosent work for me.

hope you can help me solve the issue
best regards
Cm1234

 

[bdreed35]

Second one should be this:

time(val(left({table.field},2)) + int(val(right({table.field},2)) / 60),remainder(val(right({table.field},2)),60),0)

You can try that first and see if you get any errors back.

For the first one, please post your formula and tell me where the cursor is placed in the code after you get the error.

~Brian

 

[cm1234]

Hi again Bdreed35

Now i get the message "a string is required here"
in the second code you send through and the curser
is placed right after the first {table.field} just like the other code.
I have simply used your code and here is were i get the error>> stringvar array strTime := split({table.field}CURSER IS HERE,",");
numbervar hours := val(strTime[1]) + int(val(strTime[2]) / 60);
numbervar mins := remainder(val(strTime[2]),60);
time(hours,mins,0);
<<
Once again,hope you can help me solve the issue
best regards
Cm1234

 

[cm1234]

Hi again

I was just thinking why do i get the error
message "a string is required here" when im actually
trying to convert a oracle numberfield to a crystal timefield
????????????

regards
cm1234

 

[bdreed35]

You are getting those errors because I am treating it as a string.

Does it really store the number as 10,91 ( with the comma in the middle)?

If you drag that field onto the report, and display it by itself, how does it look?

~Brian

 

[cm1234]

Hi Bdreed35

Im sorry but your absolute right it's shown
just like you put it 10,91 ,im quit sure off
Im not in the office any more today
but will post some data output straight away tomorrow
morning.

thanks once again
regards
Cm1234

 

[synapsevampire]

Right click the field and select Browse Data, this will tell you its data type as Crystal understands it

Since converting from a string doesn't work, it's likely a Euro number format, so try:

stringvar MyTime:=totext({table.field},0,"");
stringvar array strTime := split(MyTime,",");
numbervar hours := val(strTime[1]) + int(val(strTime[2]) / 60);
numbervar mins := remainder(val(strTime[2]),60);

-k

 

[synapsevampire]

BTW, the alternative and faster method is to create a SQL Expression, View or Stored Procedure and convert it on the database itself.

-k

 

[cm1234]

Hi out there

I have just run a check on the data
output and its defantly 00,01-00,02 and so on
To synapsevampire
thanks for the tip,i'll try to work it that way round(havent much experience in working on the database)and the code gives, that there are no errors but when i try to set the formula in the report iget the following message >>A subscript must be between 1 and the size of the array<< and the cursor places it self after
the + int(val(strTime[2]

best regards
cm1234