ColdFusion Query Help

[kdennis4]

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!

[Newsgroup_User]

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!
>

[Dan_Bracuk]

Store dates and times as dates, times, and timestamps - not text. That's my suggestion.

[-__cfSearching__-]

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__-]

-==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.

[kdennis4]

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!