Calculate time elapsed

[Jami]

I am attempting to allow users to enter work performed into an Intranet site.&nbsp;&nbsp;I would like users to be able to enter time begun and time finished into this site as well as the date this work was performed.&nbsp;&nbsp;In a later report, I would like to calculate the duration among these two times..<br><br>I am using an Oracle database and currently have defined the following fields:<br>work_date (of type date)<br><br>time_begun<br>time_end<br><br>time_begun and time_end are currently just varchar2 fields.<br><br>Can anyone give me some direction on how to do this?&nbsp;&nbsp;I am also not sure where a.m. and p.m. would come into play??&nbsp;&nbsp;Could I create a select box with these options?

 

[Guest_imported]

You would have to create two parameters And set one to TimeCreated() upon load of form and another to Now() when the users submits it, then update the database with the two. I suggest using the &lt;cfapplication&gt; to hold the time created variable therefore updating the database when both variables are set. Then you will be able to calculate the differences between the two in your later report. Since you are using varchars for both you will not need to format them right away. But you will have to parse the Date out. I believe the TimeFormat(Now(),h:m:s;t) will return the time now with either A or P appended with no leading zeros and the TimeFormat(Now(),hh:mm:ss:tt) will return time now with AM and PM appended.

 

[Jami]

Ok.. First, thanks to jal4470..&nbsp;&nbsp;I want, however, the user to be able to enter the date, enter the time_began and enter the time_ended in 3 different fields, rather than these times be generated for me.&nbsp;&nbsp;I understand that in Oracle, the date field will hold both the date and time.&nbsp;&nbsp;so, here is what I have attempted:<br><br>First, I created the following two fields of type date in my Oracle table.:<br>work_date_begin<br>work_date_end <br><br>Gathered user info via form fields...&nbsp;&nbsp;Tried to set these field values to a string like so:<br><br>&lt;CFSET Full_time_begin = FORM.work_date & &quot; &quot; & FORM.time_begin_work & &quot; &quot; & FORM.begin_am&gt;<br>&lt;CFSET Full_time_end = FORM.work_date & &quot; &quot; & FORM.time_end_work & &quot; &quot; & FORM.end_am&gt;<br><br>The output of these cfset variables looks like this:<br>Full_time_begin: 08-11-00 9:30 A.M.<br>Full_time_end:&nbsp;&nbsp;&nbsp;08-11-00 10:30 P.M.<br><br>I then tried to insert these variables using the function to_date as follows:<br><br>&lt;CFQUERY datasource=&quot;Intranet&quot;&gt;<br>INSERT INTO jami.work_performed<br>(EMP_ID_FKEY, WORK_DATE_BEGIN, WORK_CLIENTID_FKEY, WORK_DATE_END, WORK_LOG, WORK_PERF_DESC)<br>VALUES (#Cookie.emp_id#, TO_DATE('#Full_time_begin#', 'MM-DD-YY HH:MI A.M.'), WORK_CLIENTID_FKEY, TO_DATE('#Full_time_end#', 'MM-DD-YY HH:MI A.M.'), '#WORK_LOG#', '#WORK_PERF_DESC#')<br>&lt;/CFQUERY&gt;<br><br>This results in this error:<br><br>Oracle Error Code = 984<br><br>ORA-00984: column not allowed here <br><br>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (14:1) to (14:31).<br><br>Is this because you cannot use a variable within the to_date function?&nbsp;&nbsp;Don't know.&nbsp;&nbsp;Any help?&nbsp;&nbsp;Would be greatly appreciated!!