Time conversion problem against oracle database 1

[Noviceboy]

Background info, I'm writing a report against an Oracle Database and I'm using Crystal 8.0.

Here's the problem.

In our database, one field is for the date entry. This is a datetime field but only the date counts and the time goes to 12:00:00. The second field is a time entry, but it is a string type field with only 4 numbers for the entry. Thus, 0100 would equal 1AM and 1300 would equal 1PM. What I'd like to do is to combine these two fields into a formula that would become one datetime conversion of both fields.

If you can help me with that, I'd appreciate it

Problem #2-

In our database, I'm trying to get an average time between our From/To Date of occurrence. Thus, there are four fields. Date1, Time1, Date2, and Time2. And you guess it, this is part of problem #1 with the time thing.

So, just to make it more clear, an example

Date1 = 10/01/00
Time1 = 0300
Date2 = 10/1/00
Time2 = 0400

Thus, an incident occurred on 10/01/00 between 0300 and 0400. So, the answer to my question would be the average which in this case is

10/1/00 0330

Partial help would be appreciated. Thanks [sig][/sig]

 

[kenhamady]

The first part will use the datetime function, which needs 6 numbers:

DateTime (Year, month, day, hours, minutes, seconds):

DateTime(
year({Datefield}) ,
month({Datefield}),
Day({Datefield}) ,
Val({timefield} [1 to 2]),
Val({timefield} [3 to 4]),
0 )



The second would require converting the Time portions of the values to Seconds (there is a function) and then adding one value to the next value in a formula using the Next() function, and then dividing the result by 2 and converting the result back to a DateTime.

Look at the &quot;Additional&quot; functions for DateTime conversion functions. [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[ynott]

Thanks Ken!!! I only had one problem and maybe someone can answer. When I used the new formula:

DateTimeToSeconds({@newdateformula})

It converted it to seconds but this only converts to time portion of the DateTime field, thus, the date is not translated out which is a problem.

For example, newdateformula1 is 10/1/00 at 0300HRS and newdateformula2 is 10/2/00 at 0300HRS. The answer for the inbetween time should be 10/1/00 1500HRs.

However, the DateTimeToSeconds only converts the time portion of the DateTime, thus, I get the wrong answer of 0300HRS.

What I need to do is convert the whole DateTime to seconds. Is this possible? Any other ideas? Thanks [sig][/sig]

 

[kenhamady]

Subtract the whole days, and multiply the result by the number of seconds in a day.

60x60x24

You can add this to the Seconds [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]