Calculate hours, mins and seconds from datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculate hours, mins and seconds from datetime

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 06:16:42

Hello

I need to be able to return the total number of hours, mins, secs...from the difference between 2 fields.
i.e

ResolvedDateTime - CreatedDateTime

I've done this in Crystal with the below but this does not take into consideration for the dates between (if there are any) only the time but I need to return it all, so if more than 1 day add this to the returned number of hours, minutes, secs...
:
TIME({ResolvedDateTime}) - TIME({CreatedDateTime})

In SQL how can I do this:

SELECT
CONVERT(VARCHAR, getdate(),8),
AVG(ResolvedDateTime - CreatedDateTime)AS [Average Call Duration],
FROM Table1

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 06:26:15

Exactly how are you wanting this to look?


Declare @d1 datetime,
	@d2 datetime
	
	
Select @d1 = '3/28/2011 23:57:39:184'
Select @d2 = '3/30/2011 06:21:42:865'


Select 
	DATEDIFF(hh,@d1,@d2) as [hours],
	DATEDIFF(mi,@d1,@d2) as [minutes],
	DATEDIFF(ss,@d1,@d2) as [seconds],
	convert(varchar,DATEDIFF(ss,@d1,@d2)/3600)+':'+
	right('00'+convert(varchar,DATEDIFF(ss,@d1,@d2)%3600/60),2)+':'+
	right('00'+convert(varchar,DATEDIFF(ss,@d1,@d2)%3600%60),2)

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 06:32:51

hi
Just want the total time from the 2 datetime fields, what are the date formats below? do these need to reflect @d1= startdatetime and @d2 = enddatetime? my datetime fields...

Select @d1 = '3/28/2011 23:57:39:184'
Select @d2 = '3/30/2011 06:21:42:865'

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 07:18:47

yes... d1 is start time, d2 is end time... i just hardcoded a 2 times so we could see the same thing

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 07:37:34

I can have a go at this but here's what I'm trying to do, I know the code isn't there and needs work yet...but I'm trying to do this...can you advice on this? Its driving me mad...
Thanks

/*I need to return the average time between the 2 datetime fields below,
the two dates can be over several days before the resolved date is completed,
but I dont want to count hours outside the office hours or weekends
*/
USE MyTable

ALTER PROCEDURE [dbo].[AverageResolvedTime]
-- Add the parameters for the stored procedure here
@Start DATETIME,
@End DATETIME

DECLARE @BusinessHours int, --between 8am and 6pm and not weekends
SET @BusinessHours=---how do I pass the opening hours here?

SELECT IDNumber,
CONVERT(CHAR(8), DATEADD(SECOND,(AVG(ResolvedDateTime - CreatedDateTime))0, 0), 8)
AS [Average Duration],
CONVERT(CHAR(8), DATEADD(SECOND,(MAX(ResolvedDateTime - CreatedDateTime))0, 0), 8)
AS [Max Duration],
CONVERT(CHAR(8), DATEADD(SECOND,(MIN(ResolvedDateTime - CreatedDateTime))0, 0), 8)
AS [Min Duration]
FROM Incident
WHERE (ResolvedDateTime >= @Start) AND (ResolvedDateTime <= @End)
AND
BusinessHours >= dateadd(dd,dateDiff(dd,0,getdate()),'08:00')
AND BusinessHours <= dateadd(dd,dateDiff(dd,0,getdate()),'18:00')

GO

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 08:44:46

That is a very different question... it takes alot more work to identify which days are 'work' days...

for example, what about holidays? what about short days (if any)?
Also, what happens if one of your dates is outside business hours (even by a couple minutes)?

Anyway... its been done here a million times... google helped me find this:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74645[/url]

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 08:50:41

In your earlier response where you used a Select statement to set the variable is this ok, can this be used same as SET. I guess I can look at business days later but does this make sense to you below to return the time between for each record...thanks

USE MyTable
GO

CREATE PROCEDURE [dbo].[AverageResolvedTime]
-- Declare start - end

AS
Declare @d1 datetime,
@d2 datetime

Select @d1 = 'CreatedDateTime'
Select @d2 = 'ResolutionDateAndTime'

Select IncidentNumber, CreatedDateTime, ResolutionDateAndTime,Priority,
DATEDIFF(hh,@d1,@d2) as [hours],
DATEDIFF(mi,@d1,@d2) as [minutes],
DATEDIFF(ss,@d1,@d2) as [seconds],
convert(varchar,DATEDIFF(ss,@d1,@d2)/3600)+':'+
right('00'+convert(varchar,DATEDIFF(ss,@d1,@d2)%3600/60),2)+':'+
right('00'+convert(varchar,DATEDIFF(ss,@d1,@d2)%3600%60),2)
FROM dbo.Incident

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 08:57:43

Whoa Whoa... i was using actual dates in my @d1 & @d2... you can't just stick a column name in there... they are datetime variables.

Anywho..


Select 
	IDNumber, 
	Priority,
	[hours] = DATEDIFF(hh,CreatedDateTime,ResolutionDateAndTime),
	[minutes] = DATEDIFF(mi,CreatedDateTime,ResolutionDateAndTime),
	[seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
	[display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+':'+
			right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+':'+
			right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2)
FROM dbo.Incident

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 09:09:24

Ooops, I'm still learning here :)...think this might be it then, I've added the 2 field to the procedure so I can pull the field into a Crystal report, so your calculation should hopfully show the datediff time between even past 24hrs so I might see for example 48hrs 20mins...look ok now?

USE Mydb
CREATE PROCEDURE [dbo].[AverageResolvedTime]
AS
Select
IncidentNumber,
CreatedDateTime,
ResolutionDateAndTime,
Priority,
[hours] = DATEDIFF(hh,CreatedDateTime,ResolutionDateAndTime),
[minutes] = DATEDIFF(mi,CreatedDateTime,ResolutionDateAndTime),
[seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
[display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+':'+
right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+':'+
right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2)
FROM dbo.Incident
ORDER BY CreatedDateTime
GO

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 09:36:44

Any chance you can get me to display the last column display with the actual time between? other than that its starting to get there...

Appreciate your help...
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 09:43:05

all you need to do is alter the junk in blue to change the [display]...


[display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+'hours '+
	right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+'minutes '+
	right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2)+'seconds'

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 10:46:18

You are a star wish I knew more than I do...one more thing please its broken well down now on Crystal Report after creating the datasource from SQL. If I wanted to find the average time for all Incidents would I be best putting AVG on the second column then turn that into time...Think I'll stick a start - end paramter on the SP and then I can filter the report daily weekly monthly...just the average I need now for the total Incidents selected, can you advice best way on this? think this would be best sepreated from the SP as a summary field...

[seconds] = AVG(DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),

Can thank you enough for you advice...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 10:53:59

you could average the [seconds] column...but you'd either need to do this the the procedure result set (in Crystal - which I don't know anything about)... or do a subquery to pull it.


USE Mydb
CREATE PROCEDURE [dbo].[AverageResolvedTime]
AS
Select 
	IncidentNumber,
	CreatedDateTime,
	ResolutionDateAndTime, 
	Priority, 
	[hours] = DATEDIFF(hh,CreatedDateTime,ResolutionDateAndTime),
	[minutes] = DATEDIFF(mi,CreatedDateTime,ResolutionDateAndTime),
	[seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
	[display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+':'+
		right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+':'+
		right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2),
	[average] = convert(varchar,averageInSeconds/3600)+':'+
		right('00'+convert(varchar,averageInSeconds %3600/60),2)+':'+
		right('00'+convert(varchar,averageInSeconds %3600%60),2)
	FROM dbo.Incident A
	Cross Join (Select averageInSeconds = Avg(DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime) FROM dbo.Incident) B

	ORDER BY CreatedDateTime
GO

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-30 : 10:58:09

I can do it in Crystal its my T-sql i need to improve on...

Can you expain what Incident A means and the B at the end:
FROM dbo.Incident A
Cross Join (Select averageInSeconds = Avg(DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime) FROM dbo.Incident) B

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 11:22:53

the section in Cross Join (<expression>) is called a 'subquery' or 'inner query' or... basically you treat it like a table, but it is a query that defines structure and value.

In this example, I used a subquery to calculate the average of all the records I'm returning (both have matching where clauses - ie. nothing). Since I join it in as a subquery, I can then use that information in the rest of the query...

I'm not sure I'm explaining this well... sorry.

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-31 : 05:37:30

Yes makes sense I need some practice though...I'm getting
Msg 156, Level 15, State 1, Procedure AverageResolvedTime, Line 18
Incorrect syntax near the keyword 'FROM'.

When I add the average stuff above, any idea checked commas...

Thanks
s

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-31 : 05:43:20

Its on line 21 on the second From statement thats throwing the error?

ALTER VIEW [dbo].[ResolvedTime]
AS
Select
IncidentNumber,
CreatedDateTime,
ResolutionDateAndTime,
Priority,
[hours] = DATEDIFF(hh,CreatedDateTime,ResolutionDateAndTime),
[minutes] = DATEDIFF(mi,CreatedDateTime,ResolutionDateAndTime),
[seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
[display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+'hours '+
right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+'minutes '+
right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2)+'seconds ',
---average time
[average] = convert(varchar,averageInSeconds/3600)+':'+
right('00'+convert(varchar,averageInSeconds %3600/60),2)+':'+
right('00'+convert(varchar,averageInSeconds %3600%60),2)
FROM dbo.Incident A
Cross Join (Select averageInSeconds = Avg(DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)
FROM dbo.Incident) B
--FROM dbo.Incident
--ORDER BY CreatedDateTime

If I comment out this line it parses ok...?
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-31 : 07:42:26

you're missing a ')'...

Avg(DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime))

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-31 : 11:20:02

Silly me...I get arithmetic overflow error when I try to stick average on the report says error when trying to convert expression to datatype int, then NULL value is eliminated by aggregate or other SET operation...

Its getting there but I defo need to add business hours somehow so that outside of the hours 8am - 8pm the durations, so that it doesn't add times after 8pm and weekends, bank hols...

I'd like to thank you again for your help, if you can advice a way forward to not include the out of office hours to the total duration that would be very helpful, not that you've not helped me enough already :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-31 : 11:26:33

the link i listed further up should be a good start on working with business day and time. I don't have anything ready made and would probably reference the link myself to put something together. As for arithmetic overflow... you may try casting your values to bigint.

The 'error' is really just a warning, and it shows anytime an aggregate funtion is used on data that includes nulls.

For example:


n
----
10
null
20

Avg(n) = (10+20)/2 --excluding nulls

--not

Avg(n) = (10+0+20)/3

Corey

I Has Returned!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-31 : 12:13:07

Ok thanks again, will take a look at the link...and will post results.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources