"number of you"??? Integer???? get it??? OK...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 only...so 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?
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? Thanks
here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.
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.
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
dbo.IntegerToDate(at.transDate) as transDate
* That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the state from table one. I tried to make it but failed,I would appreciate your help. http://66.61.28.119/test/state.asp
update state2 set state = (select code from state1 where id= select id from state1 where...I do not know how where id =(select id from state.....I do not know how
I have created a local user on Report Server Computer and the user has the administrative rights. When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ). I get the following error.
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user. -------------------------------------------------------------------------------- SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully. The system is up for a month but this problem occured today?!? What could be the problem?!?
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
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?
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 ?
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
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?
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.
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) cn.Open() da.Fill(ds) cn.Close() 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 lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.
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); Response.Write(quota);
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)
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
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.
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 sql-server-performance.com forums)
Can I put a constraint on an integer column that will only allow a certain range of numbers to be entered, or do I have to put that into the application layer only?
I'd like the range to be 0 to 30 as the only allowable values. The only thing that I could think of was to create another table and populate with 0,1,2...,30 and put a foreign key on the new column that wouldn't allow anything not in that list but I was wondering if there was a better way.
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 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! R
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) RETURNS DATETIME AS BEGIN DECLARE @IntegerToDatetime int DECLARE @time DATETIME SET @time = '2001-01-01' SET @IntegerToDatetime = CASE 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)
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.
