Crystal formula to get time

What type us the database field?

I susoect it is a date

Try just
Time({SALES_HISTORY_HEADER.BVRVADDTIME})

mlmcc

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

@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

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.

@larryh - That worked good! Thanks for your help...

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'))

Select allOpen in new window

mlmcc

@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'))

Select allOpen in new window

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

I looked in the database and no records with NULL fields

Data looks fine. Any other ideas?

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?

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

@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)

Select allOpen in new window

My results are all 12:## am which is not correct

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

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?

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

Here is a sample of the raw data (in red)

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

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