We help IT Professionals succeed at work.

Calculating date/time variables between two dates

704 Views
Last Modified: 2013-12-24
I did a search and found some examples but none that are working and none that are giving me what I want.  Just a basic DateDiff really isn't working for me either.

I have a StartDate and an EndDate in a query.  The hard data from the query could look like this:
StartDate                                       EndDate
11/11/2010 5:00:00 AM           11/12/2010 12:01:05 PM    

I need to display the difference between these two fields as:
1 day, 7 hours, 1 minute, and 5 seconds


     
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Adapted from http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=377


<!--- Simulate query values --->
<cfset qry.StartDate = createDateTime(2010, 11, 11, 5, 0, 0)>
<cfset qry.EndDate   = createDateTime(2010, 11, 12, 12, 1, 5)>

<cfset duration = fullDuration(qry.startDate, qry.EndDate)>
<cfdump var="#duration#">

<cffunction name="fullDuration" returntype="struct">
	<cfargument name="startDate" type="date">
	<cfargument name="endDate" type="date">
	<cfset var duration = structNew()>
	<cfif dateCompare(arguments.startDate, arguments.endDate) gt 0>
		<cfthrow message="StartDate cannot be greater than EndDate">
	</cfif>
	<cfset duration.years = dateDiff("yyyy", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("yyyy", -duration.years, arguments.endDate)>
	<cfset duration.days = dateDiff("d", arguments.StartDate, arguments.endDate)>
	<cfset duration.months = dateDiff("m", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("m", -duration.months, arguments.endDate)>
	<cfset duration.days = dateDiff("d", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("d", -duration.days, arguments.endDate)>
	<cfset duration.hours = dateDiff("h", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("h", -duration.hours, arguments.endDate)>
	<cfset duration.minutes = dateDiff("n", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("n", -duration.minutes, arguments.endDate)>
	<cfset duration.seconds = dateDiff("s", arguments.StartDate, arguments.endDate)>
	<cfreturn duration >
</cffunction>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
jasonduan - I like the MOD (%) approach :)
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
@leerljr68

Though both should work, I'd go with jasonduan's approach ;-)
Lee R Liddick JrReporting Analyst

Author

Commented:
Wasn't sure where to put jasonduans approach...i was working on your stuff because I understood that a little better.  Hahahaha
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Just put it in your query, substituting the column name for the variables.  Personally I'd make it separate columns (ie TotalDays, TotalHours, etc... ) instead of one big string  But it's up to you

SELECT  StartDateColumn, EndDateColumn,
             STR(DATEDIFF(day, StartDateColumn, EndDateColumn))  AS TotalDays  ,
             STR(DATEDIFF(hour, StartDateColumn, EndDateColumn % 24) + AS TotalHours ,
             ....
FROM    YourTable

You could also put it in a udf. All depends on how you want to slice it.
Lee R Liddick JrReporting Analyst

Author

Commented:
This is what I have as my query:

        <cfquery dbtype="query" name="getDuration">
        SELECT             dtEventStart, dtEventEnd,
                        STR(DATEDIFF(day, dtEventStart, dtEventEnd)) AS TotalDays,
                        STR(DATEDIFF(hour, dtEventStart, dtEventEnd % 24)) AS TotalHours,                
                        STR(DATEDIFF(minute, dtEventStart, dtEventEnd % 60)) AS TotalMinutes,                
                        STR(DATEDIFF(second, dtEventStart, dtEventEnd % 60)) AS TotalSeconds                
        FROM        qrySelEvent                    
        </cfquery>

But I am getting a CF error:
Query Of Queries syntax error.
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.

But I have a FROM statement?  I don't get it?
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
You can't do it in a QoQ.  Those are sql server only functions. So you either have to do it in your original database query, or not at all.
Lee R Liddick JrReporting Analyst

Author

Commented:
Ahhhh, well took it out of the QoQ:
        <cfquery datasource="myds" name="getDuration">
        SELECT             dtEventStart, dtEventEnd,
                        STR(DATEDIFF('d', dtEventStart, dtEventEnd)) AS TotalDays,
                        STR(DATEDIFF('h', dtEventStart, dtEventEnd % 24)) AS TotalHours,                
                        STR(DATEDIFF('n', dtEventStart, dtEventEnd % 60)) AS TotalMinutes,                
                        STR(DATEDIFF('s', dtEventStart, dtEventEnd % 60)) AS TotalSeconds                
        FROM        tbl_event
        WHERE     intEventId = '#qrySelEvent.intEventID#'
        </cfquery>

But now I get this CF error:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid parameter 1 specified for datediff.  
Lee R Liddick JrReporting Analyst

Author

Commented:
and if I leave it like it was originally shown:
                        STR(DATEDIFF(day, dtEventStart, dtEventEnd)) AS TotalDays,
                        STR(DATEDIFF(hour, dtEventStart, dtEventEnd % 24)) AS TotalHours,                
                        STR(DATEDIFF(minute, dtEventStart, dtEventEnd % 60)) AS TotalMinutes,                
                        STR(DATEDIFF(second, dtEventStart, dtEventEnd % 60)) AS TotalSeconds    
I get this:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid operator for data type. Operator equals modulo, type equals datetime.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>> (DATEDIFF('d', dtEventStart, dtEventEnd)

Sql server doesn't use quotes around the intervals (day, hour, ...).  Go back to what you had before

STR(DATEDIFF(day, dtEventStart, dtEventEnd)) AS TotalDays,
....
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Lee R Liddick JrReporting Analyst

Author

Commented:
The parenthesis was it...it all works now!  Thank you all.
Lee R Liddick JrReporting Analyst

Author

Commented:
The accepted solution is not working...we are finding this:

Start = 11/26/2010 10:08:00 AM
End = 11/26/2010 4:00:00 PM
The end result is coming up:  6 hours 52 minutes
The correct result should be 5 hours 52 minutes

Another Example:
Start = 11/23/2010 6:57:00 AM
End = 11/23/2010 7:18:00 PM
The end result is coming up:  1 hour 21minutes
The correct result should be just 21 minutes


<cfquery datasource="mydsn" name="getDuration">
   SELECT   dtEventStart, dtEventEnd,
               DATEDIFF(d, dtEventStart, dtEventEnd) AS TotalDays,
               DATEDIFF(hh, dtEventStart, dtEventEnd) % 24 AS TotalHours,
               DATEDIFF(n, dtEventStart, dtEventEnd) % 60 AS TotalMinutes
   FROM    myTable                    
   WHERE   ID = '#ID#'
</cfquery>
Outage Duration:
<cfoutput query="getDuration">
#TotalDays# day(s), #TotalHours# hour(s), #TotalMinutes# minute(s)
</cfoutput>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
At a guess, could it be a DST issue? Let me run a few tests and see what results I get.
Lee R Liddick JrReporting Analyst

Author

Commented:
I figured I would play around with your suggestion and I got that to work...
<!--- get query values --->
<cfquery datasource="myDNS" name="getDuration">
        SELECT 		dtEventStart, dtEventEnd             
        FROM        myTable                    
        WHERE     	intID = '#ID#'
</cfquery>

<cfoutput>
<cfset qry.StartDate = #getDuration.dtEventStart#>
<cfset qry.EndDate   = #getDuration.dtEventEnd#>
</cfoutput>
<cfset duration = fullDuration(qry.startDate, qry.EndDate)>

<cffunction name="fullDuration" returntype="struct">
	<cfargument name="startDate" type="date">
	<cfargument name="endDate" type="date">
	<cfset var duration = structNew()>
	<cfif dateCompare(arguments.startDate, arguments.endDate) gt 0>
		<cfthrow message="StartDate cannot be greater than EndDate">
	</cfif>
	<cfset duration.years = dateDiff("yyyy", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("yyyy", -duration.years, arguments.endDate)>
	<cfset duration.months = dateDiff("m", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("m", -duration.months, arguments.endDate)>
	<cfset duration.days = dateDiff("d", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("d", -duration.days, arguments.endDate)>
	<cfset duration.hours = dateDiff("h", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("h", -duration.hours, arguments.endDate)>
	<cfset duration.minutes = dateDiff("n", arguments.StartDate, arguments.endDate)>
	<cfset arguments.endDate = dateAdd("n", -duration.minutes, arguments.endDate)>
	<cfset duration.seconds = dateDiff("s", arguments.StartDate, arguments.endDate)>
	<cfreturn duration >
</cffunction>
 


Outage Duration:
<cfoutput>
<cfif #duration.days# gt 0>
 	#duration.days# day(s), 
</cfif>
<cfif #duration.hours# gt 0>
 	#duration.hours# hour(s), 
</cfif>
<cfif #duration.minutes# gt 0>
 	#duration.minutes# minute(s) 
</cfif>
</cfoutput>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Nah, that wasn't it.  I should have checked the proposed query more closely.  Seems MS SQL calculates the date boundaries differently than CF (ie *not* partial units). I think you'll need to use the minutes instead to get partial hours.  Either that or use the CF version

Not highly tested yet. But something like this should calculate the right hours:

ie 0 days 5 hours, 51 minutes.

SELECT   dtEventStart, dtEventEnd,
               DATEDIFF(d, dtEventStart, dtEventEnd) AS TotalDays,
               DATEDIFF(n, dtEventStart, dtEventEnd) % (24*60) /60 AS TotalHours,
               DATEDIFF(n, dtEventStart, dtEventEnd) % 60 AS TotalMinutes
FROM      myTable                    
WHERE   ID = '#ID#'

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Sorry, I didn't see your comment before posting. My remark "Nah, that wasn't it." was referring to my guess it might be a DST issue.  It wasn't ;-)
Lee R Liddick JrReporting Analyst

Author

Commented:
Yeah I figured that...no biggie, I got your original cf version to work.  Thanks for the quick response, as always, aqx!
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Anytime!
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.