Help Needed

Hi All
In a table i m having two columns timein and timeout
i want to find out the no of hrs or days from timeout and timein
plz help me
this is the data

timein timeout
9/7/2007 4:44:57 PM 24/7/2007 10:55:11 AM
6/7/2007 10:59:51 AM 10/7/2007 12:00:03 PM

Waiting for valuable reply

BabaIf i understand you correctly, you may be looking at the DATEDIFF function (explained in Books Online).

Something along the lines of SELECT DATEDIFF(D, timein, timeout) will give you the number of days difference between the 2 columns.

HTH
|||

Baba:

Do you mean the difference between columns of the same row or do you mean the difference between columns of different consecutive rows? Also, are you using SQL 2005?

|||i got it. thank u

Baba
|||Hi Kent

We are using sqlserver2005

i got the ans but one doubt is there when i tried to get the data in months it is displaying 0

and one more doubt is i want gethrs,min,ss if poss day is it possible to include all these formats in date_diff

Waiting for u r valuable reply

|||Kent
u scolded me or u need the time to reply

Baba
|||

It was not intended as scolding at all; but I need time to adjust to the requirements. Please forgive. Maybe something like this:

Code Snippet

set dateformat dmy

declare @.aTable table
( timein datetime,
timeout datetime
)
insert into @.aTable
select '9/7/2007 4:44:57 PM', '24/7/2007 10:55:11 PM' union all
select '6/7/2007 10:59:51 AM', '10/7/2007 12:00:03 PM'

select timein,
timeout,
datediff(day, 0, timeout - timein) as Days,
datediff(hh, 0, timeout - timein) % 24 as Hours,
datediff(mi, 0, timeout - timein) % 60 as Minutes,
datediff(ss, 0, timeout - timein) % 60 as Seconds
from @.aTable

/*
timein timeout Days Hours Minutes Seconds
-- - -- --
2007-07-09 16:44:57.000 2007-07-24 22:55:11.000 15 6 10 14
2007-07-06 10:59:51.000 2007-07-10 12:00:03.000 4 1 0 12
*/

|||Thank u for u r reply

Baba