DATEDIFF question

DATEDIFF question

• Tatyana S

  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
  

  
  
  

• Psycho Potato

  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

  
  
  

• Some Developers

  Hi Hifni,

  U also can use like this.

  Code Block

  declare @s as datetime

  declare @e as datetime

  set @s = '1/1/2007 10:15:01 AM'

  set @e = '1/1/2007 11:20:01 AM'

  Select convert(varchar,dateadd(ms,datediff(ms,@s,@e),'00:00:00'),114)

  
  
  

• Armin Prosch

  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.
  
  

• joe_coolish

  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.

  
  
  

• Rajul Aggarwal

  Hi,

  Why not substract StartDateTime from LastDateTime and than use

  CONVERT(datetime, Substract_result, 108) AS Diff_in_hh_mm_ss

  should do the trick

  
  

• DaveBost

  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,

   

  
  
  
• DATEDIFF question