sql function

Thanusree Duth

CONVERT(VARCHAR(5), (DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600)) + '':'' + CONVERT(VARCHAR(5), (SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60)) + '':'' + CONVERT(VARCHAR(5), (DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60)) When I used this.I got output as 0: -13: -16(negative values) But I will have to get output as 0:13:16 how to remove negative sign from datetime in sql?

Mycroft Holmes

I may have done you a disservice, seems you have changed to datetime, well done. Not sure if it will work with datetime but you might try ABS()

Never underestimate the power of human stupidity RAH

J4amieC

you've still not read the forum guidlines, you've still not learnt to use descriptive subject lines, you've still not learned to keep it in 1 thread. You'll learn nothing from this response so i'll just paste the correction. Im not going to bother testing it, so check the syntax and try to understand it. CONVERT(VARCHAR(5), ABS((DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600))) + '':'' + CONVERT(VARCHAR(5), ABS((SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60))) + '':'' + CONVERT(VARCHAR(5), ABS((DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60)))

Luc Pattyn

Maybe if you swap both datetime arguments in DATEDIFF, your problem is gone? :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Jeremy Hutchinson

It looks like you're trying to get the hour:minute:second difference between two datetime values. The simplest way to do that is subtract them, then convert that value to char and apply the appropriate formatting.

declare @t1 datetime, @t2 datetime

select @t1 = GETDATE()

--Select @t2 = @t1 + 1:02:03 (hh:mm:ss)
select @t2 = DATEADD(hour, 1, dateadd(minute, 2, dateadd(second, 3, @t1)))

select convert(varchar, @t2-@t1, 108)

You can find other formatting options here[^]