get datediff as hr:mt:sec - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

get datediff as hr:mt:sec

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-09-30 : 00:32:47

Hi
Using this query
SELECT DATEDIFF(hour,'2008-09-27 08:30:00.000','2008-09-27 13:00:02.000')
we could get 5 as hour difference
Is there any query by which I can get the difference as 4:30:2

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 00:45:28

[code]SELECT CAST(DATEDIFF(ss,'2008-09-27 08:30:00.000','2008-09-27 13:00:02.000')/3600 AS varchar(2)) + ':' +CAST((DATEDIFF(ss,'2008-09-27 08:30:00.000','2008-09-27 13:00:02.000')%3600)/60 AS varchar(2))+':'+CAST((DATEDIFF(ss,'2008-09-27 08:30:00.000','2008-09-27 13:00:02.000')%3600)%60AS varchar(2))[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-30 : 09:28:08

[code]select
TimeDiff = convert(varchar(10),ET-ST,108)
from
( -- Test Data
select ST = convert(datetime,'2008-09-27 08:30:00.000'),
ET = convert(datetime,'2008-09-27 13:00:02.000')
) a

Results:

TimeDiff
----------
04:30:02

(1 row(s) affected)

[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:39:39

If you are expecting time differences larger than 24 hours, see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources