Solved

Crystal formula to get time

Posted on 2015-02-03
20
275 Views
Last Modified: 2015-02-06
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
Comment
Question by:Gerhardpet
  • 7
  • 6
  • 4
  • +1
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What type us the database field?

I susoect it is a date

Try just
Time({SALES_HISTORY_HEADER.BVRVADDTIME})

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
@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
 
LVL 2

Expert Comment

by:larryh
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
@larryh - That worked good! Thanks for your help...
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
@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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
I looked in the database and no records with NULL fields

Data looks fine. Any other ideas?
0
 
LVL 2

Expert Comment

by:larryh
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Accepted Solution

by:
James0628 earned 400 total points
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
@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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 2

Expert Comment

by:larryh
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
Here is a sample of the raw data (in red)
raw data
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now