Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Crystal Reprots Formula Number to time

I have a field in my database that I want to convert to time format

The field type is: Number and the data looks like this
15001595 which I want to convert to 3:00:15 pm
9360344 which I want to convert to 9:36:03 am

I do not need the milliseconds.

Thank you in advance for your help!
0
Gerhardpet
Asked:
Gerhardpet
  • 4
  • 4
1 Solution
 
GJParkerCommented:
Try this

//@NumberToTime
Local StringVar strTime := strReverse(cstr({Your_Field},0,''));
Local StringVar secs := strReverse(Mid(strTime,3,2));
Local StringVar mins := strReverse(Mid(strTime,5,2));
Local StringVar hrs := strReverse(Mid(strTime,7,2));

Time(hrs & ':' & mins & ':' & secs)

Open in new window


Thsi will return the time which you can then format to display in any format you wish, for teh format you have asked for just change to 12 hour

HTH
0
 
GerhardpetAuthor Commented:
It works when I'm using crystal 10 while editing the report but when I run the report in the ERP Crystal Run Time it does not work and I get the following error
 Error with ERP Crystal Run TIme
0
 
GJParkerCommented:
What version is the runtime in your ERP system ?
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
GerhardpetAuthor Commented:
Version 10
0
 
GJParkerCommented:
do you have any numbers in a different format, this formula relies on your times being held as

1 or 2 digit hours
2 digit mins
2 digits secs
2 digit msecs

can you run the same report that is failing through the designer ?
0
 
GerhardpetAuthor Commented:
I found some of the time fields in the database are blank. How can avoid blank records?

I get this error in designer when I run the report against all records. If I only run the report against a few records it does work

I need to skip blank or invalid records
 Designer error
0
 
GJParkerCommented:
Try this

//@NumberToTime
Local StringVar strTime;

If IsNull(Your Field) or Your test for blank Then
    strTime := '00000000'
Else
    strTime := strReverse(cstr({@number},0,''));

Local StringVar secs := strReverse(Mid(strTime,3,2));
Local StringVar mins := strReverse(Mid(strTime,5,2));
Local StringVar hrs := strReverse(Mid(strTime,7,2));

Time(hrs & ':' & mins & ':' & secs)
0
 
GerhardpetAuthor Commented:
That works. Thank you!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now