date difference (DateDiff) question?

[cfdeveloper]

Hello everybody,

I'm working on this script to get the date difference between two dates in minutes. After doing that; I want to then deduct/subtract the value returned by the datediff function from another date value.

Here is what I'm trying to do:

Reading the two date values from a table:

<cfset timeCallPlacedOnHold = queryTmp.HoldDateTime>
<cfset timeOnHoldReleased = queryTmp.ReleasedDateTime>

For some reason the dates retuned are in this format:

Ex: "2004-09-08 12:28:56.0"

I can't understand why it adds the ".0" after the time when the value stored in the database is "2004-09-08 12:28:56"


<!--- Date diff in minutes --->
<cfset totalDateDiff = datediff ("n",TimeCallPlacedOnHold,TimeOnHoldReleased)>

Now, I'm taking the value returned by the dateDiff function and subtracting it from another date

DateAdd("n",-totalDateDiff,queryTmp.FixDateTime)

The dates returned are not correct. I don't want I'm doing wrong. Can somebody please point out what I should be doing to correct this.

Best regards
cfcoder

 

[r937]

you could/should be doing the date arithmetic in the sql, it's faster

what does the query look like?

rudy

http://r937.com/

SQL Consulting

 

[varnix]

are you doing a dateformat or timeformat on the information before you output?

That will certainly affect how it looks, the date/time format you've shown is the default, that's why you get the trailing .0 from CF.

I wrote a quick and dirty query basically doing the same thing that you're doing above and everything is being returned correctly.

Now if we assume that you put someone on hold at 10:15 on 9/9/04 and you took them off hold at say 10:20 on 9/9/04, that would result in a totalDateDiff of 5.

Now depending on what your 'fixdate' is, lets say its 9/9/04 13:23, your dateadd function would subtract five minutes from that date/time, resuling in 9/9/04 13:18.

So I'm really not sure what is wrong overall, perhaps if you were to show us some of the data and the expected result that might help.