|
|
Hi all,
I wanna know about, r Sql Server 2000 support the Booleon data type, if yes plz tell how can it use...
and also tell about some common Data type like datatime,varchar,numeric...
Thanx in advance
Sajjad
|
|
|
|
|
imshally81 wrote:
Sql Server 2000 support the Booleon data type
Yes, in SQL Server it is called bit
imshally81 wrote:
tell about some common Data type
That's a vig vague. What do you want to know?
The base datatypes are:
binary Bigint bit Char datetime decimal Float image Int Money
nchar Ntext nvarchar Numeric Real smalldatetime smallint smallmoney sql_variant sysname text timestamp tinyint varbinary varchar uniqueidentifier
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanx for reply,
I just wanna know small introduction of common data types and their usage....
Sajjad
|
|
|
|
|
|
|
I have a table on an SQLServer 2000 box with a DateTime field called CallTime.
I want to select all the records between 19:00 and 06:59 over a period of days
Is there a more efficent way of doing the select in a stored procedure than this,
WHERE (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN
DATEPART(hh, '19:00') * 60 + DATEPART(mi, '19:00') AND DATEPART(hh, '23:59') * 60 + DATEPART(mi,
'23:59')) OR (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN
DATEPART(hh, '00:00') * 60 + DATEPART(mi, '00:00') AND DATEPART(hh, '06:59') * 60 + DATEPART(mi,
'06:59'))
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
|
Thanks Colin.
Having to split the WHERE up into before midnight and after midnight seemed to a bit clunky, but it'll do the job. I really hate T-SQL somedays, feels so primitive. Here's hoping that SQLServer2005 has a few useful enhancements.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
|
Colin Angus Mackay wrote:
Another thought occurs to me...
You could create an extra column to store the minutes (or seconds) since midnight. It is updated by a trigger on the table. Your query would be less complex and probably run faster too.
Thanks. I hadn't thought of that one. I may look into implementing that for the next release of the software. I'll stick with the query for now as I'm only using it to build a View to produce a report and I don't want to make too many changes.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
Could you subtract out 7 hours from each DateTime value, then convert the result to a string formatting the time in AM/PM format and just keep the ones that have a PM as their time component by using a LIKE comparison? Not sure just what this SQL would look like, but I think it work.
where to_char((DateTime - 10584000),'YYYYMMDDHHMISSAM') LIKE '_____________PM'
The value of 1058400 is how many seconds are in 7 hours.
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi all,
I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type).
how can i manage the Relationship.....
plz discuss in detail.....
thanx in advance
Sajjad Rizvi
|
|
|
|
|
"E. F. Codd"...put that in Google (or even better, Amazon) and read the results.
|
|
|
|
|
|
Hi
does ne1 know how to put an if statement with in sql query ??
im looking on doing something like the below
Select school_id,class_id from student
if school_id = 1
where left(class_id,1) = @class_val
else
where left(class_id,2) = @class_val
Any ideas
Thanks
Si
|
|
|
|
|
Try the CASE statement.
e.g.
select school_id,class_id,
CASE school_id
WHEN 1 THEN left(class_id,1)
ELSE left(class_id,2)
as exp1
from student
where
exp1 = @class_val
I think this will work. Sorry but I can't test it now on my machine but try and let me know.
|
|
|
|
|
Only to show you more examples with CASE:
select nf_item.nf_number,
case nf_item.nf_unit_abbr
when 'KGS' then nf_item.wgt_net/1000
when 'TON' then nf_item.wgt_net
end as wgt_net,
case nf_item.currency_id
when '0795' then nf_item.price_per_unit_c
else nf_item.price_unit_conv nf_master.exchange_rate
from nf_item with(nolock) where nf_item.ts_print >= 2345345345
|
|
|
|
|
Hello all.
I'm almost getting my head to the wall  because I can't get to the solution for a query I need to do.
Is anyone here wanting to help me ?
Let me explain the problem:
I have a table named T1 with the following structure:
ID | Subject | ParentID
This table represents info in a form of tree and when I'm at the top branch the ID and ParentID fields have the same value.
If I have the following data
ID Subject ParentID
1 Sub1 1
2 Sub2 2
3 Sub1_1 1
4 Sub1_2 1
5 Sub1_1_1 3
I want to make a query with the following result:
ID|Subject|NumberOfChildRecords
e.g.:
1 | Sub1 | 2
2 | Sub2 | 0
3 | Sub1_1 | 1
4 | Sub1_2 | 0
5 | Sub1_1_1 | 0
Is this simple to do ?
Thanks in advance for the attention you are having reading this.
|
|
|
|
|
I dont think it is simple... but you could certainly try something like:
Here is how you would do it using NW:
select a.employeeid, count(b.employeeid) from employees a, employees b
where a.employeeid = b.reportsto group by a.employeeid
What we have is a self referencing table. What we need to do is essentially add the table twice in the from clause with two seperate aliases and then correlate the two in the where. I should be using ANSI standard joins here, but it was quick.
so for you we need something like
select a.id, count(b.id) from T1 a, T1 b where
a.id = b.parentid group by a.id;
Let me know if this works.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
I migrate the microsoft class : SqlHelper to OracleHelper, i used in this class ODP .NET 10..
I need some person to do the test. if there is someone who is interested by this this request ,please send me an email
at nizar@myway.com and i will response him.
thnx
ENSI TUNISIA
|
|
|
|
|
we are developing an ASP.NET application with SqlServer at backend..
there are an supplier and about 3000 customer, and each customer has about 3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is bigger then sale amount, we are updating its stock, and we increment suppliers stock. The query is like that :
DECLARE @StockAmount int
SELECT @StockAmount = Amont FROM Stocks WHERE CustomerId = @BuyerCustomerId
IF @StockAmount > @SaleAmount
UPDATE Stocks SET amount = @StockAmount - @SaleAmount WHERE CustomerId = @BuyerCustomerId
SELECT @SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId = @SellerCustomerId
UPDATE Stocks SET amount = @StockAmount + @SaleAmount WHERE CustomerId = @SellerCustomerId
i know, each customer has got not so many users and a concurrency problem seems to be not a big possibility. but there is just a 1 supplier record and i think conlicts are possible. how can i alter this problem. after a research, i found
SELECT @SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE CustomerId = @SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction finishes. any approaches are appreciated. thanks.
|
|
|
|
|
Hi,
I want to execute a transact SQL like below
Declare @local as varchar(30)
Declare @LocalStr as varchar(50)
Set @LocalStr ='Select @local =ColumnName, * from tablename where Condition'
Exec(@LocalStr)
But it gives @local not declare error
or how can I assign the value of columnName to local variable, It should be Transact (Dynamic) as conditions are dynamic
Thanks in advance
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
