Solved

Calculating date/time variables between two dates

Posted on 2010-11-12
20
673 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


     
0
Comment
Question by:Lee R Liddick Jr
  • 11
  • 8
20 Comments
 
LVL 11

Assisted Solution

by:jasonduan
jasonduan earned 250 total points
ID: 34122793
DECLARE @StartDate DATETIME                                      
DECLARE @EndDate DATETIME
SET @StartDate = '11/11/2010 5:00:00 AM'
SET @EndDate = '11/12/2010 12:01:05 PM'

SELECT
      STR(DATEDIFF(day, @StartDate, @EndDate)) + ' day(s)'
      +
      STR(DATEDIFF(hour, @StartDate, @EndDate) % 24) + ' hour(s)'
      +
      STR(DATEDIFF(minute, @StartDate, @EndDate) % 60) + ' minute(s)'
      +      
      STR(DATEDIFF(second, @StartDate, @EndDate) % 60) + ' second(s)'
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34122797
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

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34122812
jasonduan - I like the MOD (%) approach :)
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 52

Expert Comment

by:_agx_
ID: 34122881
@leerljr68

Though both should work, I'd go with jasonduan's approach ;-)
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34123097
Wasn't sure where to put jasonduans approach...i was working on your stuff because I understood that a little better.  Hahahaha
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34123254
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.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34123827
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?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34123968
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.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34124181
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.  
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34124213
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34124223
>> (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,
....
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 34124239
>> ]Invalid operator for data type

The parenthesis are just misplaced. Also, you can get rid of STR

This works

SELECT
DATEDIFF(day, dtEventStart, dtEventEnd) AS TotalDays,
DATEDIFF(hour, dtEventStart, dtEventEnd) % 24 AS TotalHours,                
DATEDIFF(minute, dtEventStart, dtEventEnd) % 60 AS TotalMinutes,                
DATEDIFF(second, dtEventStart, dtEventEnd) % 60 AS TotalSeconds    
FROM      TableName
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34124296
The parenthesis was it...it all works now!  Thank you all.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34250680
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

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34250834
At a guess, could it be a DST issue? Let me run a few tests and see what results I get.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34250880
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

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34251012
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#'

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34251028
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 ;-)
0
 

Author Comment

by:Lee R Liddick Jr
ID: 34251050
Yeah I figured that...no biggie, I got your original cf version to work.  Thanks for the quick response, as always, aqx!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34251095
Anytime!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to generate a csr to request an intermediate ca on os x 3 35
htaccess restrict subdomain 4 118
Coldfusion retrieve dynamic values 1 36
coldfusion cfloop help 6 32
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

831 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