[Webinar] Streamline your web hosting managementRegister Today

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

Crystal formula to get time

I have this formula that I found elsewhere to get the time form a field which does not work

CStr(Time(Left ({SALES_HISTORY_HEADER.BVRVADDTIME},5)), "h:mm tt", "AM", "PM")

Open in new window


I get "A string is required here"

In the Pervasive database documentation the field data type is COBOL NumericSA
0
Gerhardpet
Asked:
Gerhardpet
  • 7
  • 6
  • 4
  • +1
2 Solutions
 
mlmccCommented:
What type us the database field?

I susoect it is a date

Try just
Time({SALES_HISTORY_HEADER.BVRVADDTIME})

mlmcc
0
 
James0628Commented:
That formula looks like it was meant to extract the time from a string field that started with the time (eg: "12:34 some additional text").  Your field is apparently not a string (and even if it was, that formula would only work if the time was the very first thing in the string).  You can check the Field Explorer in CR to see what data type CR shows for BVRVADDTIME.  If you don't see the data type listed after the field names, right-click on a field in the list and there should be a "Show field type" option.

 If the field type is not Time or DateTime, put the field on the report and see what you get.

 James
0
 
GerhardpetAuthor Commented:
@Jame,
The field type is Number and it consist of 1325120400 which would be 1h 25mm 12sec the rest is milliseconds (1:25 pm)

All I need is the h and mm
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
larryhSr. Software EngineerCommented:
Well, it appears that even though the field type is Number, it is actually a string representation of the time.  This would be my suggestion even though it omits the milliseconds.

Time(val(mid(ToText({SALES_HISTORY_HEADER.BVRVADDTIME},0,"",""),1,2)),
val(mid(ToText({SALES_HISTORY_HEADER.BVRVADDTIME},0,"",""),3,2)) ,
val(mid(ToText({SALES_HISTORY_HEADER.BVRVADDTIME},0,"",""),5,2)))

We basically convert the number to a string (ToText) and then parse out the hour, minute and seconds and convert each of those to a numeric parameter to the Time function.  You would then place this formula into the report and format it as a Time field.

Alternatively, you could create a variable to hold the text version of the number so you don't have to do repeated calls to ToText.
0
 
GerhardpetAuthor Commented:
@larryh - That worked good! Thanks for your help...
0
 
mlmccCommented:
Does that work for morning times before 10?

I suspect you may have to modify the formula to handle times before 10am since they will not have the leading 0

For instance if you had a time like 125120400  which is 1:25:12 AM the above formula returns 12:51:20 PM.

Try this code that adjusts for morning times
Local NumberVar myTime := 125120400;
Local StringVar strTime;
strTime := Left(Right('0'& ToText(myTime,0,"",""),10),6);

Time(Picture(strTime,'xx:xx:xx'))

Open in new window


mlmcc
0
 
GerhardpetAuthor Commented:
@mlmcc You are right! When I run with records in the morning I get "Hours must be between 0 and 23"

But now I tried your formula and all I get " Crystal Reports error code 0x800479b in Report: ReadRecords: Bad time format string"

Here is the current formula with my field
Local NumberVar myTime := {SALES_HISTORY_HEADER.BVRVADDTIME};
Local StringVar strTime;
strTime := Left(Right('0'& ToText(myTime,0,"",""),10),6);

Time(Picture(strTime,'xx:xx:xx'))

Open in new window

0
 
mlmccCommented:
Any chance the time filed is NULL?

Can you build a quick report and just put that field on it?
Do you see any data that looks bad?

mlmcc
0
 
GerhardpetAuthor Commented:
I looked in the database and no records with NULL fields

Data looks fine. Any other ideas?
0
 
larryhSr. Software EngineerCommented:
This is a long shot but I see that the zero you are sticking at the beginning of the string uses single quotes while the other strings use double quotes.  Does that make a difference?
0
 
James0628Commented:
FWIW, you could also use CStr/ToText to add the leading 0, by using a format with ten 0's, like CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000").  You could incorporate that into one of the other suggestions.  Personally, I'd probably use a one line formula like

Time (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx"))

 However, mlmcc's suggestion should also work, so there's still the question of what's causing the error.

 If the problem is just that the field is sometimes null, you can use IsNull to check for that, and replace the null time with something else (eg. midnight).  For example:

if IsNull ({SALES_HISTORY_HEADER.BVRVADDTIME}) then
  Time (0, 0, 0)
else
  Time (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx"))


 OTOH, if the problem is not null values,  you could use IsTime to look for invalid time values, and just replace them with a default value (eg. midnight).  For example:

if IsTime (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx")) then
  Time (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx"))
else
  Time (0, 0, 0)


 But if you don't know what's actually causing the error, you should probably try to identify the problem values.  Maybe there's a problem with your data that needs to be addressed.  You could use a record selection formula similar to the following to look for problem values:

IsNull ({SALES_HISTORY_HEADER.BVRVADDTIME}) or
not IsTime (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx"))

 James
0
 
mlmccCommented:
larryh - Thanks for catching that.  In Crystal single or double quotes can be used for strings.  That allows you to use a single or double quote without having to escape for it.
However that line should have been

strTime := Left(Right('0'& ToText(myTime,0,""),10),6);

or to be consistent
strTime := Left(Right('0'& ToText(myTime,0,''),10),6);

mlmcc
0
 
GerhardpetAuthor Commented:
@James,
This works without any error but my results are not correct
if IsTime (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx")) then
  Time (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "0000000000"), 6), "xx:xx:xx"))
else
  Time (0, 0, 0)

Open in new window


My results are all 12:## am which is not correct
date results
0
 
mlmccCommented:
Are some of the times less than 9 in length?

Can you just run a report showing the time field we are converting or add the time field to that report

mlmcc
0
 
larryhSr. Software EngineerCommented:
How about a sample report where you display your formula value plus the raw number field as well.  That way we can compare the data with the results?
0
 
James0628Commented:
What they said.  It seems that some of your time values are formatted differently.  If so, you need to figure out what's different about them.  For example, if a time only included 2 digits for the fractions of a second, 1325120400 would become 13251204, which would be interpreted by our formulas as 12:13 AM.

 James
0
 
GerhardpetAuthor Commented:
Here is a sample of the raw data (in red)
raw data
0
 
mlmccCommented:
Try using James formula but change the format string to 8 0's rather than 10.
Time (Picture (Left (CStr ({SALES_HISTORY_HEADER.BVRVADDTIME}, "00000000"), 6), "xx:xx:xx"))

Open in new window


You could also use my formula changing the 10 to 8
Local NumberVar myTime := {SALES_HISTORY_HEADER.BVRVADDTIME};
Local StringVar strTime;
strTime := Left(Right('0'& ToText(myTime,0,''),8),6);

Time(Picture(strTime,'xx:xx:xx'))

Open in new window


mlmcc
0
 
James0628Commented:
Yeah, I agree.  In your earlier post, you said there were 10 digits (1325120400), but, looking at those samples, it seems that you only have 8 digits.  The formulas that mlmcc and I posted would need to be adjusted accordingly.

 Hopefully the 10 digit value was a typo or something.  If your data actually includes both -- Some values with 8 digits and some with 10 -- that could be a problem.  For example, 10000000 could be either 10 AM (8 digits) or 12:10 AM (10 digits).  How would you know which interpretation was correct?

 James
0
 
GerhardpetAuthor Commented:
Ok that was a typo on my part. Not sure why I added the 00 at the end.

Now it is working.

I have requested attention to this question to reassign the point and select the correct answer

Thanks for all of the help and patients
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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