String to time conversion 1

[Toffeeman]

Oracle DB, Crystal vs 8.0.1

I am trying to convert a string (Varchar2 (4)) into a time field within crystal reports.

The data is stored in the database as '0900' and I want to convert into a time field of 09:00 (which I can then concatenate to a similar date field to get a datetime).

I am using the formula:

time(picture({VPM_ACT_REQUEST.AR_ENT_TM},"xx:xx&quot)

where VPM_ACT_REQUEST.AR_ENT_TM is my db field.

However I keep on getting the error:

Bad Time format String

I have used this formula before without any problem, and cannot see why I am suddenly getting this error.

Any ideas would be greatly appreciated.

 

[bdreed35]

You can try this in your formula:

Time(iif(length({VPM_ACT_REQUEST.AR_ENT_TM})=4,
          Val(Left({VPM_ACT_REQUEST.AR_ENT_TM},2)),
          Val(Left({VPM_ACT_REQUEST.AR_ENT_TM},1))),
     Val(Right({VPM_ACT_REQUEST.AR_ENT_TM},2)),
     00);

~Brian

 

[Toffeeman]

Thanks bdreed35, that works a dream.