We help IT Professionals succeed at work.

How to get time difference from the start to the end of a quiz.

dawes4000
dawes4000 asked
on
618 Views
Last Modified: 2013-12-24
I am creating a quiz an need to get the time from start to finish.
If I use StartTime = now() my DB throws the error: [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '2007'
DateFormat(now(),"m-dd-yy") gives me a date no time.
DaeDiff("n","StartDate","EndDate") throws an error: java.util.Date format incorrect
Comment
Watch Question

Have you tried StartTime=CreateODBCDateTime(Now())?

Author

Commented:
StartTime=CreateODBCDateTime(Now())
produces same error:[Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '2007'

Author

Commented:
This is the complete code. I've checked the datatype of StartTime in the DB it's "dateTime" Length 8
      
<cfset addDate = CreateODBCDateTime(now())>
<cfset StartTime = CreateODBCDateTime(now())>
      <!---<cfset StartTime = #TimeFormat(now(),"hh:mm:sstt")#>--->
            <cfquery name="qryUpdateLogin" datasource="DB">
                  UPDATE tblQuiz
                  SET  Fname='#form.Fname#', Lname='#form.Lname#', addDate='#addDate#', StartTime='#StartTime#'
                  WHERE ID = '#ID#'
            </cfquery>
CERTIFIED EXPERT

Commented:

<cfset start = GetTickCount()>

do stuff


<cfset end = GetTickCount()>
<cfoutput>Elapsed time:  #numberformat(val((end-start)/1000),'9999999.00')# Seconds</cfoutput>

Are the locales set properly (i.e. the same) on the SQL server and the CF server?

Author

Commented:
SidFishes,
GetTickCount returns an integer, does it provide the integer based on system time?

Author

Commented:
elvistheprince
not sure what you mean.
CERTIFIED EXPERT

Commented:
nope...what I gave you just gives you elapsed time

seemed to me the simplest way to do what you wanted

Then just insert into your db
elapsedtime = #numberformat(val((end-start)/1000),'9999999.00')#

testdate  '#dateFormat(Now(),'mm/dd/yyyy')#'


you can also probably just use

<cfqueryparam type="cf_sql_timestamp" value="#StartTime#"> (note: cf_sql_date won't insert the time)

IMHO you should ALWAYS use <cfqueryparam..

Author

Commented:
SidFishes,
I need to store Start and End in the DB. I will get overflow, there is not a data type that fits TickCount. If I use TickCount I have to store it as int or numeric for the pending calculation to work.
CERTIFIED EXPERT

Commented:
ok... my second suggestion should work

<cfqueryparam type="cf_sql_timestamp" value="#StartTime#"> (note: cf_sql_date won't insert the time)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
<cfoutput>#numberformat(val((EndTime-StartTime)/1000), '99999999.00')/60# Minutes</cfoutput>
I changed the datatype to varchar in the DB and ran the above calculation to test. It worked. I thought we
couldn't do math on char values, go figure.
I'm not sure what you are doing with cfqueryparam.
Do you mean:

       <cfset StartTime = #TimeFormat(now(),"hh:mm:sstt")#>
          <cfquery name="qryUpdateLogin" datasource="DB">
             <cfqueryparam type="cf_sql_timestamp" value="#StartTime#">
               UPDATE tblQuiz
               SET  Fname='#form.Fname#', Lname='#form.Lname#', addDate='#addDate#', StartTime='#StartTime#'
               WHERE ID = '#ID#'
          </cfquery>
CERTIFIED EXPERT

Commented:
see above

also see http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=part_cfm.htm

short version

cfqp provides a way of pre-formatting the data and making sure it is in the right format that the db is expecting. It also provides a great deal of protection against sql injection

ie: select * from users where userid= #form.userid#; drop table users;

this bit of code which is a simple attack would have deleted your user table and anyone who fills out the form field can do it unless you use cfquerparam...

everyone should use it every time


Author

Commented:
Wow, good to know.
StartTime=<cfqueryparam type="cf_sql_timestamp" value="#StartTime#">
Do I set StartTime before or within <cfquery>, and if needs to be set
is it just <cfset StartTime = #(now()#>?

CERTIFIED EXPERT

Commented:
you could do <cfset StartTime = now()>

or simply

 StartTime=<cfqueryparam type="cf_sql_timestamp" value="#now()#">

note that you don't need ## within most cf functions unless you are displaying data (ie with cfoutput) There are exceptions like

cfswitch
cfquery
cfqueryparam
cfinvokeattribute
cfmail

and a few more probably

Author

Commented:
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
Yes, that works. The DB accepts that datatype.
Now, how do I get DateDiff in hours, miutes, seconds?
<cfoutput>#DateDiff('n',qryResults.StartTime,qryResults.EndTime)#</cfoutput>
The above seemed to be a few minutes off from actual DB data.
There isn't a TimeDiff() in there.

Author

Commented:
I suppose the following works:
<cfoutput>#DateDiff('s',qryResults.StartTime,qryResults.EndTime)/60#</cfoutput>
results are:
START: 01:08:01 PM
END: 01:15:01 PM
MINUTES: 6.98333333333  

It would be nice for a better break down.

CERTIFIED EXPERT

Commented:
check the answer here

https://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21611833.html

you'd add this after your query

                  <cfscript>
/**
 * Converts a given number of days, hours, minutes, OR seconds to a struct of days, hours, minutes, AND seconds.
 *
 * @param timespan        The timespan to convert.
 * @return Returns a structure.
 * @author Dave Pomerance (dpomerance@mos.org)
 * @version 1, January 7, 2002
 */
function CreateTimeStruct(timespan) {
    var timestruct = StructNew();
    var mask = "s";

    if (ArrayLen(Arguments) gte 2) mask = Arguments[2];

      // if timespan isn't an integer, convert mask towards s until timespan is an integer or mask is s
      while (Int(timespan) neq timespan AND mask neq "s") {
            if (mask eq "d") {
                  timespan = timespan * 24;
                  mask = "h";
            } else if (mask eq "h") {
                  timespan = timespan * 60;
                  mask = "m";
            } else if (mask eq "m") {
                  timespan = timespan * 60;
                  mask = "s";
            }
      }
      
      // only 4 allowed values for mask - if not one of those, return blank struct
      if (ListFind("d,h,m,s", mask)) {
            // compute seconds
            if (mask eq "s") {
                  timestruct.s = (timespan mod 60) + (timespan - Int(timespan));
                  timespan = int(timespan/60);
                  mask = "m";
            } else timestruct.s = 0;
            // compute minutes
            if (mask eq "m") {
                  timestruct.m = timespan mod 60;
                  timespan = int(timespan/60);
                  mask = "h";
            } else timestruct.m = 0;
            // compute hours, days
            if (mask eq "h") {
                  timestruct.h = timespan mod 24;
                  timestruct.d = int(timespan/24);
            } else {
                  timestruct.h = 0;
                  timestruct.d = timespan;
            }
      }
      
      return timestruct;
}
</cfscript>

            
                  <cfset startdate = now()>
                  <cfset enddate = "01/24/2007 16:30:30">
                  
                  <cfset thedifference = datediff("n",qryResults.StartTime,qryResults.EndTime)>
<cfset timestruct = CreateTimeStruct(thedifference, "m")>
<cfoutput>
#timestruct.d# days, #timestruct.h# hours, #timestruct.m# minutes, #timestruct.s# seconds
</cfoutput>
CERTIFIED EXPERT

Commented:
oops.. remove
      <cfset startdate = now()>
               <cfset enddate = "01/24/2007 16:30:30">

they were for testing

Author

Commented:
<cfset days = datediff("d",qryResults.StartTime,qryResults.EndTime)>
<cfset hours = datediff("h",qryResults.StartTime,qryResults.EndTime)>
<cfset minutes = datediff("n",qryResults.StartTime,qryResults.EndTime)>
<cfset seconds = datediff("s",qryResults.StartTime,qryResults.EndTime)>
<cfset timestruct = CreateTimeStruct(days, "d")>
<cfset timestruct = CreateTimeStruct(hours, "h")>
<cfset timestruct = CreateTimeStruct(minutes, "m")>
<cfset timestruct = CreateTimeStruct(seconds, "s")>

Yes, that works.
Thanks to Sid Fishes and Dave Pomerance.
CERTIFIED EXPERT

Commented:
glad to help

(don't forget to close the q ;)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.