Get Time

Question

Post reply

Get Time

MoreanK

Mr or Mrs. 500

Points: 540
More actions
February 24, 2005 at 3:13 am

#74865

Dear all,
I want to know only the minute and second of duration time but I don't know how to use the datediff function. Pls help me!
eg. select datediff( ???, getdate(),'2/24/2005 1:12:00 PM')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Something like this?

SELECT

STR(FLOOR(datediff( ss, getdate(),'20050224 13:12:00')/60),2) +

':' +

REPLACE(STR(datediff( ss, getdate(),'20050224 13:12:00')%60, 2),' ','') TimeElapsed

TimeElapsed

----------------------

46:59

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Damn, I'm out of my head today

SELECT

REPLACE(STR(FLOOR(datediff( ss, GETDATE(),'20050224 13:12:00')/60),2), ' ','0') +

':' +

REPLACE(STR(datediff( ss, GETDATE(),'20050224 13:12:00')%60, 2),' ','0') TimeElapsed

makes it look nice.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MoreanK Mr or Mrs. 500 Points: 540 More actions · Answer 3

Dear Frank,

I've tested it according to your sql statement but I can't get the result. I only get this : **:**. Pls help me again!

Best Regards,

MK.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Sorry, for editing around today:

CREATE TABLE #temp

(

c1 DATETIME

)

INSERT INTO #temp VALUES('20050224 12:53:00')

INSERT INTO #temp VALUES('20050224 14:53:00')

SELECT

REPLACE(STR(CASE

WHEN datediff(ss,c1, GETDATE())>0

THEN datediff(ss,c1, GETDATE())/60

ELSE datediff(ss,GETDATE(),c1)/60 END % 60,2), ' ','0')

+ ':'

+

REPLACE(STR(CASE

WHEN datediff(ss,c1, GETDATE())>0

THEN datediff(ss,c1, GETDATE())%60

ELSE datediff(ss,GETDATE(),c1)%60 END,2), ' ','0') TimeDifference

FROM #temp

DROP TABLE #temp

TimeDifference

------------------

64:30

55:30

(2 row(s) affected)

Can you have time differences greater than 1 day?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 5

If the duration between the two dates is within an hour then

DECLARE @date1 datetime, @date2 datetime

SET @date1 = '20050224 13:12:04'

SET @date2 = '20050224 13:47:52'

SELECT RIGHT(CONVERT(varchar,DATEADD(second,DATEDIFF(second,@date1,@date2),0),108),5)

Otherwise you will have to use what Frank posted

Far away is close at hand in the images of elsewhere.
Anon.

MoreanK Mr or Mrs. 500 Points: 540 More actions · Answer 6

Dear Frank and David,

Now I got it! Thank you so much for your help.

Best Regards,

MK.