DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10