|
Hey there!
It is a bit hard to convert it to dateTime from just 24hrs Time format. From the little knowledge you have, you could play around with the following syntax but I am sure that it won't return you the proper result.
Syntax: convert(varchar(24),cast(1530 as datetime),8)
It would be wise to just do it on the front end if you are using it. In C#, you can achieve this by:
DateTime _dteDateTime = Convert.ToDateTime("1530").ToString("hh:mm");
If you are using VB.Net, I think the syntax was:
FORMAT(Node, "yyyy-mm-dd")
Hope that this helps.
------------------------------------------------------------------------
If there was no SQL, there would be no sequels to programming.
|
|
|
|
|
Hi,
MAX() function returns the max value from a specific column of the table, but i want to get the max value from a row.
Assume the following table.
col1 col2 col3 col4 col5 col6
0 7 9 3 4
5 6 10 8 1
I want to update the col6 whith the max value in that row. For the above example col6 will have 9 in first row and 10 in second.
I don't know the exact number of columns in the table. There can be any number of columns and i have to find the max from all columns.
Can anybody help me in this regard.
Thanks in advance.
Regards,
Sulaman Riaz
|
|
|
|
|
I think its kind of a given in SQL that you know how many columns you're dealing with and the name of each one. If you're a blindingly clever lunatic you maybe be able to query the metadata of the table and produce some uber proc to do this.
Personally, I'd have a go with a bit of code.
Regards,
Rob Philpott.
|
|
|
|
|
Can anyone recomend me a good SQL Script generator (A free one)?
Ive tryed:
SQL 2000 Sript generator and SQL Express Generator (Both of them create scripts in the wrong worder, and when executing the script, it fails) I CANT BELEIVE IT
Ive also tried Database publishing wizard (This one doesnt works because of functions that return table results i havent tryed removing those functions but i guess that it will create scripts in the wrong order)
Thanks for any suggestions
Alexei Rodriguez
Alexei Rodriguez
|
|
|
|
|
|
Hi All,
I have 2 reports, one a main report and the other a subreport.
My question is, Will i be able to change or refer to a control(item) in the main report depending on the value of an item in the subreport, when the subreport is being run? If yes how?
Any comments are appreciated.
Thanks in advance.
Payal
|
|
|
|
|
Hello....
I am trying to understand this parameter "Connection LifeTime" in the connection string.
The definition says "Connection Lifetime - When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime.
A value of zero (0) causes pooled connections to have the maximum connection timeout. "
Connection Lifetime - if I set the value for this to be 5 secs, does it mean that after I close my connection, and gets returned to the pool, and if the difference of the creation time and the returned time is greater than 5 secs, will the connection be available for use immediately, from the connection pool? Or do I loose this connection from the pool?
Or am I reading it incorrectly? What is the maximum connection timeout?
Please advice...
Thank You
|
|
|
|
|
Simply, connection timeouts refer to the time it takes the Server to run a query and generate a result. If you reach connection timeout, your conenction to the database will be terminated and you will need to re-initialise the connection to the database.
When we talk about Pooling, what that refers to is that when you try and make a connection to the database, the server tries to make contact with the database engine. If you set the timeout value to 5, it will try for 5 seconds and if no connection has been established, it will return you an error (Connection Timeout). Which means that you didnt have a connection to begin with. If you do establish a connection and then leave the connection (Close()), you will again lose the connection and you will need to go through the same process of establishing the connection.
Hope that this helps.
Regards,
Ali
|
|
|
|
|
Hello
This is the statement, which I am use in Sql Base
select IF (@SCAN (@UPPER(enota),'MI') ,1 , 60 ) , 2
from
members;
How can I execute similiar statement in SQL Server?
Thanks
lp
|
|
|
|
|
|
Hello!
I am writing a stored procedure that should insert a row in a table that it's name is sent to it as a parameter.
I do it this way:
CREATE PROCEDURE myProcedure
@tablename ntext,
AS
INSERT INTO @tablename(...)
and I get an error "Must declare the table variable "@tablename"".
What should I do?
Thanks in advance!
Have a nice day!
|
|
|
|
|
Solved the problem. I'm using EXEC instead of an explicit INSERT.
Have a nice day!
|
|
|
|
|
Which one is feasible to store data?
Sutheesh Ramachandran
Cybernet Software Systems,
T Nagar, Chennai 17.
|
|
|
|
|
That's something of a broad question there. XML doesn't store anything - it's a mark up language. You store your data as XML, and you could do this in a file or a database or something.
SQL is a query language for querying relational databases. Typically databases are geared to storing things in a tabular fashion.
If the data you want to store is hierarchial in nature then XML is a good choice, if tabular then a database table is a good choice.
Sql Server 2005 has quite good support for XML and you can use XQuery within stored procs to query stuff stored as XML.
Regards,
Rob Philpott.
|
|
|
|
|
hi there,
i have a column contain some values..i just want to copy the data of column to same column after orginal data.
means if the column contains 1,2,3 and i copy the same column in it then it will be 1,2,3,1,2,3
Hope u understand
what can do ???
Regards and thankx in advnace
Koolprasad2003 
Be a good listener...Because Opprtunity knoughts softly...N-Joy
|
|
|
|
|
What datatype is the column?
Regards,
Rob Philpott.
|
|
|
|
|
koolprasad2003 wrote: means if the column contains 1,2,3 and i copy the same column in it then it will be 1,2,3,1,2,3
I assume from this the column is a varchar or char, so
update table1<br />
set col1 = col1 + ',' + col1
will do the job. Watch out for nulls though.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Guys!
I am using the following Statements:
Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
From ( select ItemPrice, Count(*) as ItemPriceCount
from #Mytable
group by ItemPrice
Having Count(*) = 1) as MyTableWithUniqueItemPrices
which returns me the unique value in the table. The only problem is, how do I also get the itemID without the SQL Server listing all the values in the Table. My Original Question is pasted below for reference.
Thanks once again to all the guys who had helped me earlier.
Ali.
-----------------------------------------------------------------------------------------------
ORIGINAL QUESTION:
=================
I tried to look around but I could not find an answer for this question. I need to run an SQl Query which retuns me the highest Unique Value and the lowest Unique value from the same column. For example. Lets say the column name is itemPrice, and has the following values: 10, 10, 11, 12, 12, 13, 14, 14 respectively. How can I get the 11 and 13 as the result of my query. I have tried DISTINCT, MIN, MAX and HAVING but to no avail. I am using SQL Server 2000.
|
|
|
|
|
did you check my answer in your original posted message?
Hope it helps.
I Love SQL
|
|
|
|
|
Hey Blue Boy,
Thanks for the reply. I did and it returns the min and max value properly. But I am not getting the ID for the item.
Thanks for the help.
|
|
|
|
|
Hey Blue Boy,
Sorry, wrote it the wrong way, it returns the id if I add it but it did not return the unique values I was looking for. The query given by Syed returns the values I want but it does not return the ID for the product.
Thanks.
|
|
|
|
|
tell me the columns of the table.
Does your table have columns like this:
ID itemPrice <br />
-----------------------<br />
1 | 10<br />
2 | 10<br />
3 | 11<br />
4 | 12<br />
5 | 12<br />
6 | 13<br />
7 | 14<br />
8 | 14
I Love SQL
|
|
|
|
|
Hey Blue Boy! 
Thanks for the help mate. Yes the columns and the values that you are displaying are correct. But from the resultset, what I need is ID: 3 and value 11 as Min(itemPrice), and ID: 6 and value 13 as Max(itemPrice). What I need is the lowest unique or distinct (whatever you would like to call it) and the maximum value which does not have similar values (e.g 11 and 13 have only one entry where as others have more than one).
The other query that I mentioned in the last post returns me the values 11 and 13 but it is not returning the ID's (3 and 6) respectively.
I hope I make some sense now. Sorry if I failed to explain it properly earlier.
Thanks and kind regards,
Ali
|
|
|
|
|
I guess you are looking for this
select id,itemprice from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1<br />
<br />
and id in( (select top 1 id from Table1 where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id asc ) <br />
, (select top 1 id from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id desc))
I Love SQL
|
|
|
|
|
Since we are sure that our Min/Max prices will be unique so we can write a subquery for retrieving the IDs, here's an addition to my original query.
Select MinUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MinUniquePrice ) as MinPriceID,
MaxUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MaxUniquePrice ) as MaxPriceID
From
(
Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
From
( select ItemPrice, Count(*) as ItemPriceCount
from #Mytable
group by ItemPrice
Having Count(*) = 1
) as MyTableWithUniqueItemPrices
) As MyTableWithMinMaxItemPrices
But Blue_boy's approach was better. I really liked it.
Regards,
Mehroz
modified on Wednesday, April 2, 2008 3:55 PM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
