How To Take Integer Of A Number?

for example which function can i use to convert the real number into the corresponding integers?

4.5 ->5


'nother Integer Question For A Number Of You

"number of you"??? Integer???? get it???'s early/late/whenever...

without the search function, I am unable to easily find what I am nearly sure is an easy one...

but...I am converting a CSV string to a table of integers using a stolen fn_CsvToInt function that returns a table of integers...found THAT on here earlier, b'gawd ;)

In any case, I am using IF (ISNUMERIC(yada) = 1) to validate that the sub-string of the input CSV list of number is, in fact, a number...but what I am after is INTEGERS if someone sends in some stupid stuff (though I am SURE none of MY users would do such a thing ;) ) like:select * from fn_CsvToInt('1,43,5.7,byte_me,100)

what I want to return is 1, 43, 100 (ignoring the 5.7 and 'byte_me' entries in the list). However, using the ISNUMERIC allows the sneaky '5.7' entry past, and then I get an error trying to convert it to an integer later.

Any easy way to check for integers? Or do I need to whip out a quick fn_IsInteger UDF for my evil and exclusionary purposes?


View Replies View Related

Cut Decimals And Return The Integer Of A Number

Hi, I was wondering how I can have the integer og a number that haves decimals.
I tried with FLOOR and ROUND function but it didn't work.
Does anyone knows how to do this?

View Replies View Related

Is It Possible To Get The Month Name With Only An Integer Representation Of The Number

Is it possible to get the month name with only an integer representation of the number.

i.e January ,February..... the DATENAME only takes a date as a value.
thanks in advance

View Replies View Related

Split A Decimal Number Into The Integer Part And The Fraction Part

I have a table with a column named measurement decimal(18,1).  If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return  2.5.  So if the value after the decimal point is 0, I only want the stored proc to return the integer portion.  Is there a sql function that I can use to determine what the fraction part of the decimal value is?  In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.

View Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time


I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
                     then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View Replies View Related

Limitations In Term Of Number Of Tasks And Number Of Columns


I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

View Replies View Related

How To Change A Decimal Number To Percent Format Number?

in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?


View Replies View Related

Integer / Integer

Hi All,

If I run "SELECT 610/100", query analyser returns 6! (wrong)

If I run "SELECT 610.0/100", query analyser returns 6.100000! (correct)

I know I can get the correct result by running "SELECT Cast(610.0 as Float)/100", but why doesn't the first example return 6.1?

Is there some setting on my server which says Integer / Integer = Integer ?



View Replies View Related

UniqueID Vs Integer

My question is, i guess, a simple one:
When is it more convenient to use a uniqueid Data Type instead of a smallint, tinyint, bigint, etc (any type of int) when the field is gonna be the primary key for the table?

View Replies View Related

GUID Vs Integer

I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)

View Replies View Related

Getting Value From Database Into Integer

I have an sql statmetn that counts all the votes in the table. i need this to calculate the quota. My problem is how to i get the value(i.e the count of the votes) into the area thats colored red below?
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota =  (count(Vote) + 1) / ((11) + 1);

View Replies View Related

Inserting An Integer

Public Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn)
Return ds
End Function
Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.

View Replies View Related

Help With Integer Field

hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.

View Replies View Related

How Do I Format An Integer

How do I format an integer. Add commas.1234565 1,234,565TIA

View Replies View Related

Default Value For Integer

I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.

View Replies View Related

Format Integer

I have some integers I want to format prefixed with zeros, e.g. 1235 would become 001235 and 445 would become 000445,

View Replies View Related

Integer, Varchar And IN

In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname
from category
where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."

Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?

(same question is also posted at forums)

View Replies View Related

Integer Datatype

Hi all,

I have a situation where I will have to insert a value(whole number) into the table where the the value is more than what the Integer can hold , I was wondering is there any other datatype which i can use other than integer

Thanks in advance.

Sanjeev Kumar

View Replies View Related

Even Integer Function

Dear Fellow SQL Server 7 Administrators,

I was wondering if there is a way to call back in a SQL Query Analyzer all even or odd integers in a user table? By this I mean, 1,3,5,7,9,11,13,15,17, etc. If you have any ideas, I would be greatly appreciative. I am currently trying to construct a function statement but if someone has a better plan or one already drawn up, let me know.


View Replies View Related

Integer To Datetime UDF

Trying to write the most effective UDF to convert INT to Datetime.
We have a column from a table on AS400 that is a INT type. Some are 4, 5, 6 ,7 digits. I have the 4 digits right. I need to fix it for 5 and 6 digits.

ALTER FUNCTION IntegerToDatetime (@int INT)
DECLARE @IntegerToDatetime int
SET @time = '2001-01-01'
SET @IntegerToDatetime =
WHEN LEN(@int) = 7
THEN '20' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),4,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),6,2) AS int)
WHEN LEN(@int) = 6
THEN '19' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),1,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),3,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),5,2) AS int)
WHEN LEN(@int) = 5
THEN '200' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),1,1) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),4,2) AS int)
WHEN LEN(@int) = 4 THEN cast(@time AS INT)

RETURN (@IntegerToDatetime )



1900-01-02 00:00:00.000
1900-05-27 00:00:00.000

View Replies View Related

Integer Question

Hi there!

I am building an application that counnts clicks on an ad. I use the integer field to count them but someone told me there is a limit number this field can count - is that correct? if so, is there a solution for my problem?
Thanks and happy 2007!

View Replies View Related

Add A Fixed Value (integer)

philippe writes "Hello ,
i am just starting with sql...

I have a table: Table_user

Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first

I will appreciate some tips...."

View Replies View Related

Integer To Datetime

I use this to get all users from active directory.


FROM openquery(


,'SELECT name, AccountExpires FROM


WHERE AccountExpires IS NOT NULL AND AccountExpires not in('0','0x7FFFFFFFFFFFFFFF','9223372036854775807','')
AccountExpires returns values like 128514708000000000 (This value represents the number of 100 nanosecond intervals since January 1, 1601 )
How do I convert this value to Datetime?

select getdate() returns a value like 2008-04-18 10:00:00.00 and that's how I'd like my AccountExpires

View Replies View Related

How To Set Integer Value In Buffer

I am trying to set a decimal value to the pipelinecolumn buffer, but it doesnt get set, and the value is NULL.

Here is the portion of the code of what I am trying to do:

if (columnInfos[x].colName.EndsWith("_CRC"))


int a;

a_cmp tst = new a_cmp();

a= tst.a_crc32(inputbufferstream); this function returns a integer value

buffer.SetDecimal(colInfo.bufferColumnIndex, Convert.ToDecimal(a));


Please let me know how to set a decimal value in the buffer.


View Replies View Related

How To Declare Integer Var In VB&&>NET

 PLeaset le tme knwo

View Replies View Related

Unique Integer IDs


I wanted to hear from you - if you've used any unique integer generation technique in the context of a disconnected smart client/local data store and have been successful with it.

Any ideas/suggestions will be great.




View Replies View Related

Datetime To Integer

I was just told that it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.

View Replies View Related

Insert Integer Null Value

 Hi,I have SQL Server DB, and I tried to insert some data. I used StorProc to do the insertion as follows:ALTER PROCEDURE dbo.InsertPage        (    @PageID int,    @ParID int,    @ChiID int,    @PageContent ntext    )AS        INSERT INTO MP_Page                            (PageID, ParID, ChiID, PageContent)      VALUES     (@PageID,@ParID,@ChiID,@PageContent)    RETURNThe problem happened when I tired to insert int null value in field ChiID which is allow null and here is my codeprivate void InsertPage(PagesDB PageDB, out pagedetails pages)        {            if (txtChild.Text == "")            {                int varChildID;                varChildID = int.Parse(txtChild.Text.Trim());                pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), varChildID,                int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim());            }            else            {                pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), int.Parse(txtChild.Text.Trim()),                int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim());            }                PageDB.InsertPage(pages);        } the error message says Input string was not in a correct format.Any idea ??Thank you  

View Replies View Related

Size Of Integer In SQL Database

Hi All,
What is the size of  "Integer" in SQL database.
Thanking you

View Replies View Related

Stored Proc Integer

I have this block of code that is supposed to go out to a SQL2k5 Express DB, get the number value from a record based on a request.querystring containing the table ID number.I get the request.querystring and store it in a variable. By nature this is stored as a String data type.Then I try to convert the string into an integer using both the "Convert.ToInt32(IDNum)" and the "Int32.TryParse(Request.QueryString("ID"), IDNum)" methods.The IDNum variable is to be used to inject into a stored proc as shown below. conn = New SqlConnection(ConfigurationManager.ConnectionStrings("database_connection").ConnectionString)conn.Open()cmd = New SqlCommand("GetMyNumber", conn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.Add("@MyNumber", Data.SqlDbType.Int).Value = IDNumSession("EndResult") = cmd.ExecuteScalarcmd.Dispose()conn.Dispose()As you can see the result is to be stored into a session variable to be used later.My query works just fine when created as an actual query and I supply the numerical value of the record. So I know that's working. I know it is connecting to the db based on the debugging I've done. I just, for some reason, cannot get that data type to convert. So my questions are:1. How do I convert the queried ID number to an integer so that I can use it in my stored proc?2. In the end, can the result be stored in the session variable as I have shown or are the session variables only for string types? 

View Replies View Related

Can't Convert Record To Integer

Hi All
I'm getting an error that says that this can't be converted to an integer.
Here is the line that gets the error. dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()
I have also tried.  dt = (DataView)(EventDataSource1.Select(dssa);
I am programming in VB
here is teh rest of my code.
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()

View Replies View Related

Copyrights 2005-15, All rights reserved