GeekInterview.com
   Home |  Tech FAQ  |   Interview Questions |  Placement Papers |  Tech Articles |  Learn |  Freelance Projects |  Online Testing |  Geeks Talk |  Job Postings |  Knowledge Base | Site Search |  Add/Ask Question

GeekInterview.com  >  Tech FAQs  >  SQL Server
Go To First  |  Previous Question  |  Next Question 
 SQL Server  |  Question 2 of 55    Print  
I have a table in sql server with two fields named 'start_time' and 'stop_time'. both fields have datatype set as 'datetime'. now I want to subtract the 'start_time' from 'stop_time'? I have tried datediff() fuction,but no use. I want the correct outpput of date as well as time .
Need help as early as possible.

  
Total Answers and Comments: 4 Last Update: June 02, 2008     Asked by: mun25_khanna 
  
 Sponsored Links

 
 Best Rated Answer

No best answer available. Please pick the good answer available or submit your answer.
November 21, 2006 04:35:17   #1  
sathyavision Member Since: November 2006   Contribution: 1    

RE: I have a table in sql server with two fields named...

Hi...

Try this

select convert(varchar, datediff(hour, start_time, stop_time) / 24)+
' Days ' +
convert(varchar, datediff(hour, start_time, stop_time) % 24) + ' Hrs ' 

from tbl_name


 
Is this answer useful? Yes | No
April 09, 2007 02:50:30   #2  
MSenthil_DBA Member Since: April 2007   Contribution: 1    

RE: I have a table in sql server with two fields named...
Hi,

I don't know the problem, try this one

select datediff(hh, start_time, stop_time)  from table

gives difference hours between two time

 
Is this answer useful? Yes | No
May 11, 2007 01:30:04   #3  
goldjoy        

RE: I have a table in sql server with two fields named...
declare @starttime datetime
declare @timediff bigint
set @starttime='11-May-2007'
SELECT @timediff = DATEDIFF(millisecond, @starttime, getdate())
select @timediff%1000 --for ml sec part
select (@timediff/1000)%60 --for sec part
select (@timediff/(1000*60))%60 --for min part
select (@timediff/(1000*3600))%60 --for hour part

and concatenate the results

 
Is this answer useful? Yes | No
June 02, 2008 12:19:16   #4  
avbhaskar Member Since: May 2008   Contribution: 2    

RE: I have a table in sql server with two fields named 'start_time' and 'stop_time'. both fields have datatype set as 'datetime'. now I want to subtract the 'start_time' from 'stop_time'? I have tried datediff() fuction,but no use. I want the correct outp

Try this example:

declare @startdatetime datetime
declare @stopdatetime datetime
declare @diffdays int
declare @diffhours int
declare @diffmins int
-- assumption is startdate <= stopdate always
SET @startdatetime = '2008-06-01 00:00:00.00'
SET @stopdatetime = '2008-06-02 11:35:43.793'

SET @diffdays = DATEDIFF(dd,@startdatetime,@stopdatetime)
SET @diffhours = (DATEDIFF(hh,@startdatetime,@stopdatetime) - @diffdays*24)
SET @diffmins = (DATEDIFF(mi,@startdatetime,@stopdatetime)- @diffhours*60-@diffdays*24*60)

SELECT @startdatetime 'start-date', @stopdatetime 'stop-date',
CONVERT(VARCHAR(4), @diffdays) + ' DAY(S) ' + CONVERT(VARCHAR(4), @diffhours) + ' HOUR(S) ' + CONVERT(VARCHAR(4), @diffmins)
+ ' MINUTE(S) ' as 'runtime'


 
Is this answer useful? Yes | No


 
Go To Top


 Sponsored Links

 




About Us  |   Privacy Policy  |   Terms and Conditions  |   Contact  |   Site Map  |   Add Question  |   Propose Category  |   RSS Feeds  |   Articles Sitemap  |   Site Updates  |   Add Resource

Copyright © 2005 - 2008 GeekInterview.com. All Rights Reserved
Page copy protected against web site content infringement by Copyscape