TIME SPECIAL FORMULA

[rossallen]

In SQL Database using Crystal V9 I am trying to show time field RT_TIME correctly which is stored in the database as a number field so that 150759 I want to show as 3:07:59 PM Which is the best formula to use

 

[lbass]

Try:

time(val(left(totext({table.RT_TIME},0,""),2)),val(mid(totext({table.RT_TIME},0,""),3,2)),val(mid(totext({table.RT_TIME},0,""),5,2)))

-LB

 

[synapsevampire]

I think that you'll get in trouble with LB's formula with single digit AM hours, instead use:

if len(totext({table.field},0,"")) = 6 then
time(val(left(totext({table.RT_TIME},0,""),2)),val(mid(totext({table.RT_TIME},0,""),3,2)),val(mid(totext({table.RT_TIME},0,""),5,2)))
else
if len(totext({table.field},0,"")) = 5 then
time(val(left(totext({table.RT_TIME},0,""),1)),val(mid(totext({table.RT_TIME},0,""),2,2)),val(mid(totext({table.RT_TIME},0,""),4,2)))

-k

 

[rossallen]

Thank you both synapsevampire and lbass for your replys, works fine.