Crystal Reports 11.5

[socalgm]

I am trying to convert a varchar field that data stored looks like time (02:30:00). i need to convert and then sum the values. Tried using the Val({ContHist.DURATION}) it will sum only the hours and not the Minutes.
Help please

 

[lbass]

The following will give you the time in seconds:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),Time({table.varchar})))

You can then place this in the detail section and right click and insert a sum on it.

-LB

 

[socalgm]

Thanks LB, but this yields a "0" value. the issue is the field is a varchar type. The data looks like time (01:30:00). I am trying to extract the value of "1.5", so that I can SUM all values in order to report correctly. Currently I can get a value of "1.00", missing the 1/2 hour values. I inserted you suggestion, but it yields me only a 0.00 value...... Lost!

 

[lbass]

I understand what you are looking for and the formula should have returned the number of seconds. Please do two things:
1-Confirm that the field is a string by browsing the field to see the data type.
2-Post the exact formula you tried.

-LB

 

[socalgm]

Yes, the field is a VARCHAR(14) and it does show up in crystal as such.

if isnumeric(left({ContHist.DURATION},2)) then
tonumber(left({ContHist.DURATION},2))+tonumber(right({ContHist.DURATION},2))/60 else
tonumber(left({ContHist.DURATION},1))+tonumber(right({ContHist.DURATION},2))/60

 

[lbass]

Ok, now try MY formula, exactly as posted without using your formula conversion. Just plug in {ContHist.DURATION} for {table.varchar}.

-LB

 

[socalgm]

I did as you said and get a "Bad rime format string" error when trying to save the formula. I assume that I have some data in the duration field that is not formatted like I expected?

 

[lbass]

Please show examples of the various ways the field displays.

-LB

 

[socalgm]

In the attached .png file, note that the column under the total hours field is like 14:30:00 and it then converts to 14.00 hours, the daily hours are just a SUM of column at the left and getting no minutes. Thank you for you help.

if isnumeric(left({ContHist.DURATION},2)) then
tonumber(left({ContHist.DURATION},2))+tonumber(right({ContHist.DURATION},2))/60 else
tonumber(left({ContHist.DURATION},1))+tonumber(right({ContHist.DURATION},2))/60

 

[lbass]

I am asking to see what the duration field looks like without any formula conversions. It looks from your formula that it sometimes displays like:
4:30:00, for example.

-LB

 

[lbass]

1 - Can the duration field be null?

2 - Please post MY formula exactly as you implemented it, by copying it into this thread, so I can troubleshoot it. It should look like my post on 2 Nov 17 20:13.

-LB