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

help me....

hi everyone

i got 2 questions plz

1- how to use the datadiff function ....plz write the code

2- is there a Trim function or statment that cuts the unneeded spaces before and after the word

thanx ...

Hi there,
1. http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp?frame=true
samples imcluded :-)
2. What do you mean by unneeded ? You can trail characters, by using the function which I wrote some time ago:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
--Coded by Jens Suessmeyer, 2005 Available on http://www.sqlserver2005.de
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

RETURN @.strValue
END

Could be called by: Select dbo.fn_removetrailingchars (SomeColumn,CHAR(32),1) FROM Sometable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanx for answering

but what i mean is a function or starment Called trim which removes the spaces ....and i think it should be used in the insertion

for an example lets say wa have a string " hi " i want to remove the spaces before and after hi

so how can i use it ?

|||Hi,

if you don't want to use my function, simply use LTRIM(RTRIM(Expression))

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

help me plz!

I am pretty new to the subject...i would really appreciate if you could
help me with this doubt...

I need to write a C# code which performs the following:

a) connects to the sql server database( I dont know the procedure to
connect to the data base)

b) on a web page i need to display the list of table names and their
associated indexes in a tree view ( i.e. parent node should be the name
of the table and the child nodes should be the index names)

Plz do help me

Thanks a lotHi

You don't give which version of SQL Server you are using, but you should
find most of what you want to do in parts of the Samples that come with the
product, for instance with SQL 2005 there is a dependency explorer and other
SMO examples which will do something similar to what you require. For SQL
2000 check out the DMO examples.

John

"frien" <001frien@.gmail.comwrote in message
news:1167166884.988685.252620@.73g2000cwn.googlegro ups.com...

Quote:

Originally Posted by

>I am pretty new to the subject...i would really appreciate if you could
help me with this doubt...
>
I need to write a C# code which performs the following:
>
a) connects to the sql server database( I dont know the procedure to
connect to the data base)
>
b) on a web page i need to display the list of table names and their
associated indexes in a tree view ( i.e. parent node should be the name
of the table and the child nodes should be the index names)
>
Plz do help me
>
Thanks a lot
>

Help me PLZ

Hi...

I have a problem ..I am creating an auction site the problem is when the user enter the min bid (the type of it is money) the compiler display error

cmd.Parameters.Add("@.minbid", Data.SqlDbType.money)

when I but the type of the min bid integer its work

cmd.Parameters.Add("@.minbid", Data.SqlDbType.Int)

use Decimal type and set the precision appropriately.

Also few things:

(1) Please do not multi-post.

(2) Post all therelevant code.

(3) Post the EXACT error message as it appears rather than simply saying compilor displays error or there is a problem. In order for some one to visualize the problem and help you we need as much information as we can. You have to help us first in order for us to help you.