Difference between datetime in hh:mm? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Difference between datetime in hh:mm?

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-09 : 15:47:50

I have a transaction that has a start datetime and an end datetime. I am trying to get a result that is the difference between the two datetime fields and return a result in HH:MM

I use DateDiff(Hour, Start, End) to get total hours
When I use DateDiff(Minute, Start, End) I get the total minutes and not the remainder.

Is

John

"The smoke monster is just the Others doing barbecue"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 15:50:51

try something like:
CAST(DateDiff(Hour, Start, End) AS VARCHAR(10)) + ':' + CAST(DateDiff(Minute, Start, End) % 60 AS VARCAHR(2))

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-09 : 15:56:49

Thank you. Would a similar approach apply is trying either Avg or Max?

I am trying to calc the time open for a service request in hours and minutes but then they just asked if possible to see the HH:MM of the request open the longest as well as the average of the requests.

John

"The smoke monster is just the Others doing barbecue"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 16:38:53

Sure. Depending what you want to do it might make more sense to just that the Max or Avg in Minutes, then convert that number to Hours and Minutes by using Num / 60 (for hours) or Num % 60 (for minutes).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-09 : 17:16:23

Using datediff(hh,StartDate,EndDate) is not a valid way to determine the difference in hours. Notice the errors in total hours from the example below, compared to the correct method, datediff(hh,0,EndDate-StartDate).


select
        IncorrectHours = datediff(hh,StartDate,enddate),
        Hours         = datediff(hh,0,EndDate-StartDate),
        Minutes       = datepart(minute,EndDate-StartDate),
        StartDate,
        EndDate
from
	(  -- Test Data
        Select
                StartDate = convert(datetime,'20091113 03:35:37.913'),
                EndDate   = convert(datetime,'20091113 14:14:55.637')
	union all
        Select
                StartDate = convert(datetime,'20090213 02:44:37.923'),
                EndDate   = convert(datetime,'20090715 13:24:45.837')
        ) a

Results:


IncorrectHours Hours       Minutes     StartDate                                              EndDate
-------------- ----------- ----------- ------------------------------------------------------ -----------------------
11             10          39          2009-11-13 03:35:37.913                                2009-11-13 14:14:55.637
3659           3658        40          2009-02-13 02:44:37.923                                2009-07-15 13:24:45.837

(2 row(s) affected)

CODO ERGO SUM

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-10 : 08:15:51

I appreciate the example and if I didn't see it myself, I would not have believed. I guess I should challenge our trainer more at classes.

My next step is to read a set of selected records and determine the MAX in HH:MM of the record that has the longest time to resolution. So can I use similar logic then to perform that. When I try I get the max minutes of one example and the max hours of another.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 08:26:22

From MVJ's example


select top 1
        Hours         = datediff(hh,0,EndDate-StartDate),
        Minutes       = datepart(minute,EndDate-StartDate),
        StartDate,
        EndDate
from
	(  -- Test Data
        Select
                StartDate = convert(datetime,'20091113 03:35:37.913'),
                EndDate   = convert(datetime,'20091113 14:14:55.637')
	union all
        Select
                StartDate = convert(datetime,'20090213 02:44:37.923'),
                EndDate   = convert(datetime,'20090715 13:24:45.837')
        ) a

order by datediff(hh,0,EndDate-StartDate)*60+datepart(minute,EndDate-StartDate) desc

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-10 : 09:21:17

Thank you also I will try to adapt that to the current code.

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources