Skip to main content
K
kdennis4
Participating Frequently
June 16, 2008
Question

ColdFusion Query Help

  • June 16, 2008
  • 3 replies
  • 487 views
Hello All,

I've basically got a query that starts a project like 11:57 (varchar) inserts just fine. Then I've got a project end time like 12:40 (varchar) again inserts just fine. Then I query the endtime to minus the starttime and getting bad results. Here is the example of my code:

<cfset thisProjectEnd = (totalProjectTime.projectEnd - totalProjectTime.projectStart)>

the outcome inserted into the DB is: -0.109027777777

Any clues on how to format this correctly to other than Removing Characters? Thanks in advance for any suggestions!
    This topic has been closed for replies.

    3 replies

    -
    -__cfSearching__-
    Inspiring
    June 16, 2008
    kdennis4 wrote:
    > 11:57 (varchar)

    For consistent and accurate results the values should be stored date/time objects, not varchar. Then you could use date functions calculate the difference in minutes, hours, etc.

    Barring that, you could try converting the values to datetime objects using CF's functions (see ParseDateTime, DateDiff) function. You did not mention your database version. However, most database's also have similar date functions that you could use.
    -
    -__cfSearching__-
    Inspiring
    June 16, 2008
    -==cfSearching==- wrote:
    > Barring that, you could try converting the values to datetime objects

    .. IMO, that should be a last resort. Storing the values as datetime objects is the way to go. No worries about conversion errors due to invalid varchar values.
    K
    kdennis4Author
    Participating Frequently
    June 17, 2008
    Hello All...

    I have this code implemented now, but getting really bad results:

    <cfset hours = DateDiff("h",dsn.projectStart,dsn.projectEnd)>
    <cfset minutes = DateDiff("n",dsn.projectStart,dsn.projectEnd) MOD 60>

    <!--- Add to total --->
    <cfset total = total + hours*60 + minutes>

    I'm getting a "-359" inserted into the table....Any suggestions? I would greatly appreciate it!
    D
    Dan_Bracuk
    Inspiring
    June 16, 2008
    Store dates and times as dates, times, and timestamps - not text. That's my suggestion.
    N
    Newsgroup_User
    Inspiring
    June 16, 2008
    What about setting up the db field as a date/time and perform a datediff to
    calculate the difference in time?

    "kdennis4" <webforumsuser@macromedia.com> wrote in message
    news:g365su$b5f$1@forums.macromedia.com...
    > Hello All,
    >
    > I've basically got a query that starts a project like 11:57 (varchar)
    > inserts
    > just fine. Then I've got a project end time like 12:40 (varchar) again
    > inserts
    > just fine. Then I query the endtime to minus the starttime and getting
    > bad
    > results. Here is the example of my code:
    >
    > <cfset thisProjectEnd = (totalProjectTime.projectEnd -
    > totalProjectTime.projectStart)>
    >
    > the outcome inserted into the DB is: -0.109027777777
    >
    > Any clues on how to format this correctly to other than Removing
    > Characters?
    > Thanks in advance for any suggestions!
    >


    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices