In vb.net what kind of data can I inject in a smallInt lenght 2 Data Type? Right now I am trying to pass an integer to my SP and it gives me the error "Error converting data type nvarchar to smallint" So I guess that I am not passing the right type of data or I have to cast it?
Thanks
cmdSelect.Parameters.Add("@CustomerId", "iCustomerId")
I have a dbfield which holds a smallint value for the time eg. 1406 . I have to add this value to a datetime value in another field but need the result as a datetime value.
The result would be time(int) + date(datetime) = datetime(datetime).
I need this to compare the hours between delivery and dispatch.
Thanks for your help
P
Hi,
I get smthing I did not expected from the following query.
Code Block
SELECT DATEDIFF(month, datediff( month , LoanApplication.DecidedOnCU,GETDATE()), LoanApplication.Term)
FROM LoanApplication
data type of LoanApplication.Term field is smallint
Values are like 60,20,48 etc.
data type of LoanApplication.DecidedOnCU is date time.
Is it a problem? If so How can I fix this ?
Final answer I want to get from this query is remaining months. Ex: 14, 20 etc.
Thanks
For a SQL statement in an Alias column I am am combing severalcolumns.But I am having problems with one column as it is a smallint.I get this errorSyntax error converting the nvarchar value to a column of data typesmallintMy Sql statement "Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColNamefrom Table1I was wondering is there anyway to format/convert the smallint tonvarchar, without changing the database.
View 3 Replies View RelatedNewbie question regarding a db I have inherited. A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.DocNo
has numbers that represent persons. SequenceNo has numbers that
represent specific documents associated with each person (DocNo).So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.My SELECT statement looks like this:SELECT ReadingNo, SequenceNoThis returns data like this: 5 3I would like to concatenate the SELECT statement to return like this: 5-3So I made Sql like this:SELECT ReadingNo + '-" + SequenceNo Which
returns a alias ('No Column Named') result value of 8 which is an
arithmetic result instead of a string concatination that I want.So my questions are:1.
Should the original database designer have used string data types for
these columns since they will never be used for math purposes?2.
Do I need to cast them to string data type (like nchar(4) - since
neither column will ever exceed 4 digits) to get the result I desire?3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?
Is an index based on a smallint (16 bit) really faster than an index based on an int (32 bit)
If so, how much...
Four tables
Table A: ID smallint (PK)
Text varchar(50)
Table B:ForeginID smallint (indexed - non unique)
Text varchar(50)
rowID int (PK)
Table C: ID int (PK)
Text varchar(50)
Table D:ForeginID int (indexed - non unique)
Text varchar(50)
rowID int (PK)
Table A and C contain identical data
Table B and D contain identical data
(Tables A and B were filled and then copied to Tables C and D)
Tables A/C are loaded with 64,000 records (-32,000 to 32,000)
Tables B/D are loaded with 6,400,000 records ForeginID loaded randomly with values between -32,000 and 32,000
The purpose of this test is to find out if identical queries joined on a smallint are actually faster than int based.
I ran 3 queries on each set:
- Full select
- Select on ID/Foregin ID
- Select on Table2 RowID joined to table 1
Here are the queries:
#1. Full select (smallint) - grouped to limit result set
-----------------------------------------------------------
SELECT intAID, COUNT(intBID)
FROM TESTintA
INNER JOIN TESTintB ON intAID = intBID
GROUP BY intAID
ORDER BY COUNT(intBID) desc
#2. Select on ID/Foregin ID (smallint)
------------------------------------------
SELECT intAID, intBID, strATXT, strBTXT
FROM TESTintA
INNER JOIN TESTintB ON intAID = intBID
WHERE intAID = 29120
#3. Select on Table2 RowID joined to table 1 (smallint)
------------------------------------------
SELECT intAID, intBID, strATXT, strBTXT
FROM TESTintA
INNER JOIN TESTintB ON intAID = intBID
WHERE intPK = 1050
#4. Full select (int) - grouped to limit result set
------------------------------------------
SELECT lngCID, COUNT(lngDID)
FROM TESTlngC
INNER JOIN TEXTlngD ON lngCID = lngDID
GROUP BY lngCID
ORDER BY COUNT(lngDID) desc
#5. Select on ID/Foregin ID (int)
------------------------------------------
SELECT lngCID, lngDID, strTXTC, strTXTD
FROM TESTlngC
INNER JOIN TEXTlngD ON lngCID = lngDID
WHERE lngCID = 29120
#6. Select on Table2 RowID joined to table 1 (int)
------------------------------------------
SELECT lngCID, lngDID, strTXTC, strTXTD
FROM TESTlngC
INNER JOIN TEXTlngD
ON lngCID = lngDID
WHERE intPK = 1050
Here are the results: (run multiple times to verify)
#1. Full select (smallint) - grouped to limit result set
-----------------------------------------------------------
(8 seconds) - before computing statistics on table
(13 seconds) - after computing statistics on table
#2. Select on ID/Foregin ID (smallint)
------------------------------------------
(0 seconds)
#3. Select on Table2 RowID joined to table 1 (smallint)
------------------------------------------
(0 seconds)
#4. Full select (int) - grouped to limit result set
------------------------------------------
(8 seconds) - before computing statistics on table
(7 seconds) - after computing statistics on table
#5. Select on ID/Foregin ID (int)
------------------------------------------
(0 seconds)
#6. Select on Table2 RowID joined to table 1 (int)
------------------------------------------
(0 seconds)
Conclusion: Not only is there a negligible difference in select performance, generating stats on the smallint actually makes it slower.
(perhaps there is some kind of conversion going on here behind the scenes?)
Hi all,
I found a UDF on the web to validate INT data contained in a VARCHAR field:
http://blog.sqlauthority.com/2007/08/11/sql-server-udf-validate-integer-function/
I modified it to accept NULL values and conform more closely to INT specification. Here is my modified function:
CREATE FUNCTION [dbo].[udfIsValidINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @ShiftByOne INT;
IF LEFT(@Number, 1) = '-'
SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1;
SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0)
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND LEN(@Number)<11
AND @Number NOT LIKE '%-%')
SELECT @Ret = CASE WHEN CONVERT(BIGINT,@Number) - @ShiftByOne <= 2147483647
THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidINT('2147483648')
SELECT dbo.udfIsValidINT('2147483647')
SELECT dbo.udfIsValidINT('-200')
SELECT dbo.udfIsValidINT('-2147483649')
SELECT dbo.udfIsValidINT('32900')
SELECT dbo.udfIsValidINT('1.79E+308')
GO
I also have a separate function for SMALLINT:
CREATE FUNCTION [dbo].[udfIsValidSMALLINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @ShiftByOne INT;
IF LEFT(@Number, 1) = '-'
SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1;
SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0)
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND LEN(@Number)<6
AND @Number NOT LIKE '%-%')
SELECT @Ret = CASE WHEN CONVERT(INT,@Number) - @ShiftByOne <= 32677 THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidSMALLINT('589')
SELECT dbo.udfIsValidSMALLINT('-200')
SELECT dbo.udfIsValidSMALLINT('-32900')
SELECT dbo.udfIsValidSMALLINT('32900')
SELECT dbo.udfIsValidSMALLINT('1.79E+308')
and one for TINYINT:
CREATE FUNCTION [dbo].[udfIsValidTINYINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @L TINYINT;
SET @L = LEN(@Number);
SET @Number = COALESCE(@Number,'0');
IF (PATINDEX('%[^0-9]%', @Number) = 0
AND @L>0
AND @L<4)
SELECT @Ret = CASE WHEN CONVERT(SMALLINT,@Number) < 256 THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidTINYINT('256')
SELECT dbo.udfIsValidTINYINT('-1')
SELECT dbo.udfIsValidTINYINT('0')
SELECT dbo.udfIsValidTINYINT('255')
SELECT dbo.udfIsValidTINYINT('1.79E+308')
And, finally, a separate function for DECIMAL validation:
CREATE FUNCTION [dbo].[udfIsValidDECIMAL]
(
@Number VARCHAR(100),
@Scale TINYINT,
@Precision TINYINT
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @L TINYINT, @DSI TINYINT;
SET @Number = COALESCE(@Number,'0');
IF LEFT(@Number, 1) = '-'
SELECT@Number = SUBSTRING(@Number, 2, LEN(@Number));
SET @L = LEN(@Number);
SET @DSI = @L - LEN(REPLACE(@Number,'.',''))
IF(
PATINDEX('%[^0-9.]%', @Number) = 0
ANDCHARINDEX('-', @Number) = 0
AND@DSI <= 1
AND@L>0
AND@L<=@Scale+@DSI+ CASE @DSI WHEN 1 THEN @L-CHARINDEX('.', @Number) ELSE 0 END
AND @Scale - @Precision >= CASE @DSI WHEN 1 THEN CHARINDEX('.', @Number) - 1 ELSE @L END
)
SELECT @Ret = 1
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidDECIMAL('256',2,0)
SELECT dbo.udfIsValidDECIMAL('-1',1,0)
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,17)
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,16)
SELECT dbo.udfIsValidDECIMAL('-255.0000000000000001',3,0)
SELECT dbo.udfIsValidDECIMAL('1.79E+308',9,2)
Node that the DECIMAL validation function specifically tests whether the input number can legally convert to a given decimal scale and precision. Converting a value of 0.234234 over to DECIMAL(1,0) will work, but SQL will truncate the actual decimals to fit it in that space. However, it will throw an error if you have too many whole digits.
On the whole, I was rather rushed to get these created, so there may be some errors I didn't notice. I'm interested in any improvements you guys can make to improve performance or make them cleaner.
Thanks for looking!
- Shane
I need to insert rows into a table which contains a smallint field
for time. The times are stored in that colum as integers (898,
11345, 1259, etc.) How can I enter a time like 9:15 AM into this field? I know how to display integer data in hh:mm format but I'm stumped on how I can do the reverse.
Thanks for any help offered.
Sql Server has many data types.
For Example:
smallint
Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
I want to know that
If it contains like 0 or 100 or 1000 or -200 or -2000 or more or less.
What will its actual size?
2 bytes or change with the value.
Please also mention the reference with your answer. if available.
A SQL Server 2005 db has three date related-columns (MonthGiven,
DayGiven, YearGiven) each as smallint datatype. I would like to
create a DocDate column (datetime datatype) that combines the data from
the three existing date-related columns. I have tried casting and
simple concatentation without success. ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting but
have been unable to implement info I have found in the SQL Server
Developer Center in my situation.
Hi,
I need to change the datatype of a very large table from smallint to int...
What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..
pls help on the same!!
Thanks
Cheriyan.
UPDATE P
SET
P.IsError=1
,P.IsDrawingRevNo=1
,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)'
FROM ZPTSMGR.ProjectDrawingRaw P
WHERE P.LogId=@LogId AND P.ProjectId=@ProjectId AND P.Revision > 32767 (P.Revision NOT BETWEEN -32768 AND 32767) --SMALLINT RANGE -32768 to 32767.
--DataType Range
--tinyint DataType (MinVal: 0, MaxVal: 255). Its storage size is 1 byte.
--smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes.
--int DataType -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes.
--Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.
The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value