[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Subtracting one time from another

Posted on 2000-04-07
2
Medium Priority
?
203 Views
Last Modified: 2013-12-24
Hello folks,

I am working on my thesis project for University and I am having some trouble with a particular CF task. Any help on this would be greatly appreciated.

Basically what I need to do is record the time that a user has spent on a page. The way I am approaching this is when the user enters the page I have CF record the current time in Microsoft Access, in a field called “TimeIn” and when they exit the page they go to a new page that records the time in another field called “TimeOut”

Then when I output the data,  I would like to subtract the TimeIn from the TimeOut and display the Duration as a separate number.

I am not sure on the best way to achieve this, I have tried many ways but I cannot get it to work.

I would like the desired output to look like this

Time In:  16:24:15
Time Out: 16:25:20
Duration: 1:05

Some other questions pertaining to this problem are:

How should I store the time in the Access database? Should I apply time formatting/mask to the date first or should I do this only when I output the data?

What should I set the Datatype in Access to? Should I set it to Date/Time or as a Text ?

Thank you for any help on this and if someone knows of a better way to achieve this result please let me know how to do it.

Jacques Choquette
0
Comment
Question by:jacqueschoquette
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Expert Comment

by:paulkd
ID: 2694665
I assume that you will be using Application.cfm to track the time/in and time/out from page to page...

You shold use a datetime field and populate your DB using now(), then when you need to produce a report, you can use the built-in CF date/time functions. You example only shows times - you should use date and time.
0
 
LVL 19

Accepted Solution

by:
cheekycj earned 300 total points
ID: 2694773
DateDiff
Returns the number of intervals in whole units of type Datepart by which Date1 is less than Date2.

Syntax
DateDiff(datepart, date1, date2)

datepart
One of the following strings:

yyyy -- Year
q -- Quarter
m -- Month
y -- Day of year
d -- Day
w -- Weekday
ww -- Week
h -- Hour
n -- Minute
s -- Second
date1
Date/time object in the period from 100 AD to 9999 AD.

date2
Date/time object in the period from 100 AD to 9999 AD.

Usage
If you want to know the number of days between date1 and date2, you can use either Day of Year ("y") or Day ("d").

When datepart is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If Date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

When passing date/time value as a string, make sure it is enclosed in quotes. Otherwise, it is interpreted as a number representation of a date/time object returning undesired results.

Years from 0 to 29 are interpreted as 21st century values. Years 30 to 99 are interpreted as 20th century values.

Examples
<!--- This example shows the use of DateDiff --->
....
<CFIF IsDefined("FORM.date1") and IsDefined("FORM.date2")>
    <CFIF IsDate(FORM.date1) and IsDate(FORM.date2)>
        <P>This example uses DateDiff to determine the difference
        in     <CFSWITCH EXPRESSION=#type#>
            <CFCASE VALUE="yyyy">years</CFCASE>
            <CFCASE VALUE="q">quarters</CFCASE>
            <CFCASE VALUE="m">months</CFCASE>
            <CFCASE VALUE="y">days of year</CFCASE>    
            <CFCASE VALUE="d">days</CFCASE>    
            <CFCASE VALUE="w">weekdays</CFCASE>    
            <CFCASE VALUE="ww">weeks</CFCASE>    
            <CFCASE VALUE="h">hours</CFCASE>    
            <CFCASE VALUE="n">minutes</CFCASE>    
            <CFCASE VALUE="s">seconds</CFCASE>        
            <CFDEFAULTCASE>years</CFDEFAULTCASE></CFSWITCH>
             dateparts between date1 and date2.
        <CFIF DateCompare(FORM.date1, FORM.date2) is not 0>
        <P>The difference is <CFOUTPUT>#Abs(DateDiff
          (type, FORM.date2, FORM.date1))#</CFOUTPUT>
        <CFSWITCH EXPRESSION=#type#>
            <CFCASE VALUE="yyyy">years</CFCASE>
            <CFCASE VALUE="q">quarters</CFCASE>
            <CFCASE VALUE="m">months</CFCASE>
            <CFCASE VALUE="y">days of year</CFCASE>    
            <CFCASE VALUE="d">days</CFCASE>    
            <CFCASE VALUE="w">weekdays</CFCASE>    
            <CFCASE VALUE="ww">weeks</CFCASE>    
            <CFCASE VALUE="h">hours</CFCASE>    
            <CFCASE VALUE="n">minutes</CFCASE>    
            <CFCASE VALUE="s">seconds</CFCASE>        
            <CFDEFAULTCASE>years</CFDEFAULTCASE></CFSWITCH>.
        <CFELSE>
....

DatePart
Returns the specified part of a date as an integer.

See also DateAdd and DateConvert.

Syntax
DatePart(datepart, date)

datepart
One of the following strings:

yyyy -- Year
q -- Quarter
m -- Month
y -- Day of year
d -- Day
w -- Weekday
ww -- Week
h -- Hour
n -- Minute
s -- Second
date
Any date.

Usage
Years from 0 to 29 are interpreted as 21st century values. Years 30 to 99 are interpreted as 20th century values.

When passing a date/time value as a string, make sure it is enclosed in quotes. Otherwise, it is interpreted as a number representation of a date/time object, returning undesired results.

Examples
<!--- This example shows information available from DatePart --->
<HTML>
<HEAD>
<TITLE>
DatePart Example
</TITLE>
</HEAD>

<CFSET todayDate = Now()>
<BODY>
<H3>DatePart Example</H3>

<P>Today's date is <CFOUTPUT>#todayDate#</CFOUTPUT>.

<P>Using datepart, we can extract an integer representing
the various dateparts from that value
<CFOUTPUT>
<UL>
    <LI>year: #DatePart("yyyy", todayDate)#
    <LI>quarter: #DatePart("q", todayDate)#
    <LI>month: #DatePart("m", todayDate)#
    <LI>day of year: #DatePart("y", todayDate)#
    <LI>day: #DatePart("d", todayDate)#
    <LI>weekday: #DatePart("w", todayDate)#
    <LI>week: #DatePart("ww", todayDate)#
    <LI>hour: #DatePart("h", todayDate)#
    <LI>minute: #DatePart("n", todayDate)#
    <LI>second: #DatePart("s", todayDate)#
</UL>    
</CFOUTPUT>    

</BODY>
</HTML>      



0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

649 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