Database

Convert datetime to just time (only hour)

27-Sep-08 3:06

Just noticed your message.

That article is a good one. Especially concentrate on the total cost (= estimated seconds) and how it's distributed. Then observe logical I/O amounts (don't mind so much about physical I/O).

Your question is not bothering me at all. Actually this area is one of my special interests Smile | :)

Happy coding! Wink | ;)

Mika

The need to optimize rises from a bad design

notes4we26-Sep-08 10:03

notes4we

26-Sep-08 10:03

Hello,

I have one datetime column named Hours in SQL Server Table1, which gives the output as 09/26/2008 01:00:00, 09/26/2008 02:00:00, 09/26/2008 03:00:00 till 09/26/2008 12:00:00 format.

I wanted to get just the year in int datatype from this format and I got it using (SELECT CONVERT(int, YEAR (Hours)) AS Year FROM Table1) and I am getting the correct Year as I wanted 2008. Same way I am getting month and date also. But the issue is with hour.

I want the hour also in the integer format. I mean if there is 01:00:00, then the output should just be 1 and same way the next should be 2.

Can anyone please help me to achieve this output?
I want a proper syntax for the same.
Meanwhile I will carry on the research on my part.

Thank you.

Re: Convert datetime to just time (only hour)

PIEBALDconsult26-Sep-08 10:38

PIEBALDconsult

26-Sep-08 10:38

Perhaps DATEPART ?

Re: Convert datetime to just time (only hour)

Blue_Boy26-Sep-08 23:47

Blue_Boy

26-Sep-08 23:47

try this

<br />
select CAST(DATEDIFF(minute, 0, '01:00:00') AS int) / 60

Hope it will help.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

SQL SP

Care Career26-Sep-08 9:02

Care Career

26-Sep-08 9:02

Hi

My Stored Procedure output is as below given.

Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000

I want result in two rows as below given.

Interval Avg1 Avg2
04/2008 2000 4000
05/2008 1000 3000

Please let me know how i can do this using temm table ?

Thanks
Care Career

Wendelius26-Sep-08 9:30

26-Sep-08 9:30

Based on the info you posted, the only thing I can imagine is that you should modify the output from sp to give you what want.

So, you need to include to the post, how the result is made in sp and what do you want to change

or

if you cannot change the sp, what is the logic you want to achieve with temp tables (why 04/2008 has 4000 in avg2, since there is nothing in the data combining the row "NULL NULL 4000" to the row "04/08 NULL NULL"

Mika

The need to optimize rises from a bad design

Care Career28-Sep-08 6:55

Care Career

28-Sep-08 6:55

Hi
I do not want to change my existing SP I just want output as below given.I want to remove NULL Please help..

Sr No Interval Avg1 Avg2
1 04/2008 2000 4000
2 05/2008 1000 3000

Wendelius29-Sep-08 3:41

29-Sep-08 3:41

This simply isn't possible unless there is some kind of logic why the result set should look like you described.

If your result set in the start is:

Interval Avg1 Avg2 
NULL     NULL NULL
04/08    NULL NULL
05/08    2000 NULL
NULL     1000 NULL
NULL     NULL NULL
NULL     NULL 4000
NULL     NULL 3000

and you want:

Interval Avg1 Avg2
04/2008  2000 4000
05/2008  1000 3000

Please explain for example why the last row having 3000 in Avg2 should be combined with the row having 05/08 as interval. Also why the value 2000 on the same row is moved to the row 04/08 and so on...

One crucial thing is that you think this as result sets having rows with conditions since this is the way the DBMS does it.

The need to optimize rises from a bad design

Care Career28-Sep-08 6:59

Care Career

28-Sep-08 6:59

I want to remove NULL from below given resultset.If I will remove NULL then data will come in two rows.SO how Can i do this using temp table?
please help its urgent.

Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000

Thanks
Care Career

backup from a specific table

nelsonpaixao28-Sep-08 13:33

nelsonpaixao

28-Sep-08 13:33

listen,

first i assume columns are varchar,int,int type

if column is varchar type you can use isnull() funtion like this:

select,...,isnull(avg1,'') FROM mytable

that way you will get an empty slot.
if you want to erase all row with null values type

select * from mytable where (interval <> null) and (avg1 <> null) and (avg2 <> null)

what you type is wrong, you can´t get that, columns not from the same row!!!
about temp table forget it, you don´t need that ... for now OMG | :OMG:

nelsonpaixao@yahoo.com.br

trying to help & get help

UniqueIdentifier

Mohammad Al Hoss26-Sep-08 4:44

Mohammad Al Hoss

26-Sep-08 4:44

Is it possible that two different SQL servers generate the same

UniqueIdentifier

thanks

Mohammad Al Hoss

Development To Me Is A Pleasure more than a Job

Re: UniqueIdentifier

J4amieC26-Sep-08 4:46

J4amieC

26-Sep-08 4:46

Jeopardy answer: What is the easiest question in the world to google?

"While each generated GUID is not guaranteed to be unique, the total number of unique keys (2128 or 3.4×1038) is so large that the probability of the same number being generated twice is very small. For example, consider the observable universe, which contains about 5×1022 stars; every star could then have 6.8×1015 universally unique GUIDs."

from the first google result: http://en.wikipedia.org/wiki/Globally_Unique_Identifier[^]

reza assar26-Sep-08 2:59

reza assar

26-Sep-08 2:59

hi how can i backup and restore a table?
thanks in advance

Re: backup from a specific table

Tim Carmichael26-Sep-08 5:49

Tim Carmichael

26-Sep-08 5:49

Restore the database as a copy with another name and get the data needed from the desired table.

Re: backup from a specific table

Wendelius26-Sep-08 6:45

connect with application using ado

26-Sep-08 6:45

You can use export/import or bcp utility for a specific table.

Mika

The need to optimize rises from a bad design

reza assar26-Sep-08 2:55

reza assar

26-Sep-08 2:55

hi how can i connect from an application to sql server 2005
i build up an application_role in sql server
but still cannot create trusted login and username

Re: connect with application using ado

Ashfield26-Sep-08 3:21

About + operator for string

26-Sep-08 3:21

reza assar wrote:
trusted login and username

Trusted Connection uses WINDOWS AUTHENTICATION (i.e. your windows credentials), otherwise you need to create a login in SQL Server.

Have a look at www.connectionstrings.com to learn more about connecting to sql server.

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 1:31

TALHAKOSEN

26-Sep-08 1:31

Hi all

SELECT @vSQL = 'INSERT INTO #rapor(Id) VALUES('' '+ @X + ' '')'

i want below

FOR EX

INSERT INTO #rapor(Id) VALUES('5')

BUT SQL SAYS

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' VALUES('.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' VALUES('.

like that pls help

Thanks ...

Sampiyon FENERBAHCE

Ashfield26-Sep-08 1:49

26-Sep-08 1:49

Odd, it works for me - although you get null in @vSQL if @X is null.

You will get a space each side of you value though.

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 1:54

TALHAKOSEN

26-Sep-08 1:54

ITS THE CODE BELOW

DECLARE ankcev_cursor SCROLL CURSOR FOR
SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
OPEN ankcev_cursor

declare @vSQL varchar(1000), @numrows int
DECLARE @SNO INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;

WHILE @SNO < 83
BEGIN

FETCH NEXT FROM ankcev_cursor INTO @KNO

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND
KULLANICINO = @KNO AND SORUNO=@SNO)

SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ')'' VALUES('' '+ @sonuc+ ' '')'

Execute (@vSQL)
FETCH NEXT FROM ankcev_cursor INTO @KNO

END

SET @SNO=@SNO+1;

END

CLOSE ankcev_cursor
DEALLOCATE ankcev_cursor

Sampiyon FENERBAHCE

Ashfield26-Sep-08 2:18

26-Sep-08 2:18

It wasn't complaining about the select, it was the resuklt when you tried to exec it. You had too many quotes

try this

SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ') VALUES('' '+ @sonuc+ ' '')'

Your original selct gave this (I set @sonuc to f) :

INSERT INTO #rapor(S10)' VALUES(' f ')

rather than

INSERT INTO #rapor(S10) VALUES(' f ')

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 2:22

TALHAKOSEN

26-Sep-08 2:22

thanx for helping bob

i saw now that i had a mistake

this is working below,
SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ') VALUES('''+convert(varchar, @SONUC) +''')'

an by the way how can i see what value in my variable

i mean like c# Console.WriteLine(@vSQL) and it writes

INSERT INTO #rapor(S10) VALUES(' f ')

how can do with tsql

thanks again

Sampiyon FENERBAHCE

Ashfield26-Sep-08 3:18