I'm importing data from a text file into SQL as a varchar, and I'm leaving it a varchar in its final destination table. It is essentially a price, i.e., $25.65. I'm using this price field (varchar) to perform a calculation...
Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
Hello,
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
UPDATE myTable SET field = CAST(field AS int)
i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?
thanks
i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?
thanks
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
View 2 Replies View Related
Hi,
This seems simple but can't find. Say in my report I have 3 fields(A,B and C).
In field C I want to give a calcultion based on fields A and B. Say field C value = A+B
How do you do this? In expressions I couldn't see existing report fields, it only shows dataset columns.
Sonny
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Hi guys
I need immediate help with a query that I am trying to write. I want to sum the values in a query but the field has a type of varchar and it has decimal numbers too. So if I do the query something like that, that converts the field to int, I get the error message.
I tried converting it into real or float but I get error message on that too. I need help with adding the calculatedValues and getting there sum. I would appreciate any help with that.
Thanks
-Sarah
Select SUM(Convert(int, calculatedValue))
from monitor.dbo.monHistory
where LocalTimeWithoutDst > '8/26/06' and LocalTimeWithoutDst < '8/28/06'
This is the error message I recieve:
Conversion failed when converting the varchar value '274.2' to data type int.
After reading Dan Guzman's blog entry (http://weblogs.sqlteam.com/dang/archive/2008/02/21/Dont-Bloat-Proc-Cache-with-Parameters.aspx) I started modifying some of my code to try it out and ran into a stumbling block. What size would you specific for a varchar(MAX) field?
Since a varchar max field can hold up to 2 billion chracters I really don't think I need to specify 2 billion as the size. Anyone have any ideas?
I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........
Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.
Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??
I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?
Thanks
Sumit.
What is the max field length in SQL Server 7.0 that a varchar field can be?
I think 8000. Please advise
Thanks
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement?
Thank you.
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement?
Thank you.
Hi guys,
How do you convert a date varchar field which looks like 20080525 to 2008/05/25?
What's the maximum number of characters that varchar field will take? I read somewhere that it's 8k, but I may have misunderstood.
I have to potentially store 100k+ chars in a field.
What's the best way to do this?
Thanks,
--PhB
--PhB
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
View 3 Replies View RelatedHi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
Hello Pros
I need to insert data in a varchar column to that when its displayed new lines will appear in the text, that is the field will hold text with multiple lines...
what's the most efficient way to do it ??
Any help will be highly appreciated,
Hi,I am using MSDE together with Enterprise Manager.I have a table with a field named description.This field will be filled by a web forms's textbox web control.The textbox's maxsize attribute is set to "3000" characters.What size do I have to adjust for my DB field description?Is the size of 3000 in Enterpise Manager equal to 3000 characters for the textbox?I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.
View 4 Replies View RelatedI am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric
View 3 Replies View RelatedI have a field with State and Zip (ie; CA94526) which is a varchar field. I have lots of data that is invalid and need SELECT all records that the right(myfield,5) IS NOT Numeric. Can this be done?
Thanks!
I am trying to change a text field into a varchar 8000.
I get his error message when trying to convert.
Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or
to select only those that are over the 8000 character mark?
Please advise
Thanks
Susan
Hi,
I have to change a due_date field with a varchar(10) to a datetime. I created a new table with datetime but when I imported data into the table it errored out due to the datatype. A varchar(10) going into a datetime(8).
Is there way to resolve this without losing data?. or how do you result this problem?.
Thanks for your help,
Jeff.A.
Hiya!
I'm having a problem updating a varchar(4000) column in Enterprise Manager when I open the table and try to update one particular row directly. Most of my rows have approx. 100-500 characters in this column, and I can update all of them them, but one row which has 1976 characters in this varchar column doesn't let me change data by typing directly into this column, although it will let me update other columns in the same row. Altogether, my row size has a max. of approx. 5000, below the 8060 limit, so I doubt that's the problem. What Is?
Thanks,
Sarah
Hi, all
I am seting up a table with email message, I am wondering what is the max length for varchar field. I am so reluctant to use text field, since when
I run query for the descriptiona in sql analyzer, text field cannot be fully display in column. Any tricks to share?
Thanks
Betty
We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?
Thanks ahead of time
DMW
Edit:
We are running SQL Server 2000 and the db at the time was running in simple mood
Hi,
I have a varchar field named FinancialYTDCode containing data in the format 2007F or 2008F. I want to only select the rows with the FinancialYTDCode that is the same as the current year.
Could someone please show me how I write this in my script.
Thanks
I'm trying to import some Assessor data from a text file into a table and the field for Legal Description (column 2 in the source text file) in the table is of data type varchar(max) because some of the data goes over the 8K size. I get an error on the first row of importing that refers to column 2 (see 'Initial errors' below). I read the related post and changed the size of input column 2 to 8000 and got this error. Finally I set the size of the of input column 2 to 4000 and it ran. So I'm thinking there is a limit on the size of varchar data that can be imported. Just want to clarify what that limit is and how I might go about importing this data.
Thanks, John
Error with input column 2 set to size of 8000:
Setting Destination Connection (Error)
Messages
Error 0xc0204016: DTS.Pipeline: The "output column "Column 2" (388)" has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)
Initial errors:
Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Column 2" (18)" failed because truncation occurred, and the truncation row disposition on "output column "Column 2" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "\Scux00assrdumpsSQLServerDBexportsql.txt" on data row 1.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - exportsql_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
Hi, I have the following query
SELECT *
FROM PABX
INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE)
AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3)
LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE)
WHERE
pabx.COD_cliente = 224 and
SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7')
AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2)
AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE
AND PABX.CLASSIFICA IS NULL
AND PABX.LOCALIDADE <> AUXILIAR.DESTINO
AND (BLOQUEADO = 0 OR BLOQUEADO IS NULL)
But It has a problem because when AUXILIAR.DESTINO returns null (it means there is no registry) the condition AND PABX.LOCALIDADE <> AUXILIAR.DESTINO doesn't work, like 'SAO PAULO' is different from 'NULL' but for my query no it's not even equal, and this condition ommit the results....how can I solve it ?
PS: Both auxiliar.destino and pabx.localidade is varchar(255)
Thanks
I understood that sql 2005 has EncryptByCert(varchar) function to encrypt data field. but varchar is limit by 8,000 chars long.
View 3 Replies View RelatedHello all,I have a field defined as VARCHAR(8000) yet it only accepts a maximum of 1024 characters. Does anyone know how I can save 8000 characters in a single field?Thanks,Bill.
View 2 Replies View Related