datediff problem - 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)

datediff problem

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-07-11 : 07:56:25

Hi i want to calculate the difference between two date. I have the following example

select datediff(ss, '2003-07-08 15:03:16', '2003-07-08 10:31:41')

this gives me a value of -16295

However what i want is to get the result back in a time format like the following

04:31:35 (4 hrs 31 mins and 35 secs)

Is there a way to covert the number of seconds received from a date caluculation such as above and displaying it a suitable format like the one I want??

Any help greatly appreciated.

dsdeming

479 Posts

Posted - 2003-07-11 : 08:19:49

Try this:

DECLARE @iSeconds int,
@iMinutes int,
@iHours int

SELECT @iSeconds = DATEDIFF(ss, '2003-07-08 10:31:41', '2003-07-08 15:03:16')

SELECT @iHours = @iSeconds / 3600,
@iMinutes = ( @iSeconds - (( @iSeconds / 3600 ) * 3600 ))/ 60,
@iSeconds = @iSeconds % 60

SELECT RIGHT( '00' + CAST( @iHours AS varchar( 12 )), 2 ) + ':' +
RIGHT( '00' + CAST( @iMinutes AS varchar( 12 )), 2 ) + ':' +
RIGHT( '00' + CAST( @iSeconds AS varchar( 12 )), 2 )

One note: I changed the order of the arguments you passed to DATEDIFF so that it returns a positive value.

Dennis

pattikay
Starting Member

36 Posts

Posted - 2003-07-11 : 08:46:26

Hi zubair,
try this, hope it helps.

declare @hh int
declare @min int
declare @sec int

set @hh = (abs(datediff(ss, '2003-07-08 15:03:16', '2003-07-08 10:31:41') ))/3600
set @min = ((abs(datediff(ss, '2003-07-08 15:03:16', '2003-07-08 10:31:41') ))% 3600)/60
set @sec = ((abs(datediff(ss, '2003-07-08 15:03:16', '2003-07-08 10:31:41') ))% 3600)%60

select cast(@hh as varchar(10)) + ':' + cast(@min as varchar(2))+ ':' + cast(@sec as varchar(2))

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-07-11 : 09:23:36

hi guys,

thank you both for your solutions! They both do the job i require and i'm spoilt for choice.

Thanka again

zubair

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-07-11 : 10:44:42

declare @t int
declare @date datetime
select @t= datediff(ss, '2003-07-08 15:03:16', '2003-07-08 10:31:41')
set @date = '2003-07-08 15:03:16'
select convert(char,(dateadd(ss,-@t,cast(ltrim(rtrim(cast(year(@date) as char))) + '-' + ltrim(rtrim(cast(month(@date) as char))) + '-' + cast(day(@date) as char) as datetime))),108)

Check this out

Enjoy working

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-12 : 10:16:53

don't make it too complicated, keep it simple:

select convert(varchar(10),dateadd(ss,abs(datediff(ss, @DateTime1, @DateTime2)),0),8)

breaking it down:

1. the datediff() gives the # of seconds difference, and we take the absolute value

2. we add the # of seconds to an arbitray date -- in this case, date "0"

3. then we convert it to format 8, which displays hh:mm:ss

Of course, I will do my usual bit and suggest using:

select dateadd(ss,abs(datediff(ss, @DateTime1, @DateTime2)),0)

and letting the presentation layer display it as "hh:mm:ss", ignoring the date component. Makes SQL's job a little easier. In this case, either way is probably fine.

- Jeff

Edited by - jsmith8858 on 07/12/2003 10:19:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources