Converting a field from int to varchar during query

I have a query where I am writing the following statement:
select * from .....
where table1.field1 = table2.field2
....
....
....
field2 is a varchar field and field1 is an int field. Field2 contains the
data mapping data for field1 and also other unrelated data as character
strings.
How do I run this query without getting the error - "Syntax error converting
the varchar value 'XXXX' to a column of data type int."
Thanks,
Jignesh.
On Wed, 2 Feb 2005 13:07:04 -0800, Jig Bhakta wrote:

>I have a query where I am writing the following statement:
>select * from .....
>where table1.field1 = table2.field2
>...
>...
>...
>field2 is a varchar field and field1 is an int field. Field2 contains the
>data mapping data for field1 and also other unrelated data as character
>strings.
>How do I run this query without getting the error - "Syntax error converting
>the varchar value 'XXXX' to a column of data type int."
Hi Jignesh,
Try:
select * from .....
where CAST(table1.field1 AS varchar(10)) = table2.field2
....
....
....
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Nope, still getting the error.
"Hugo Kornelis" wrote:

> On Wed, 2 Feb 2005 13:07:04 -0800, Jig Bhakta wrote:
>
> Hi Jignesh,
> Try:
> select * from .....
> where CAST(table1.field1 AS varchar(10)) = table2.field2
> ....
> ....
> ....
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 3 Feb 2005 13:01:08 -0800, Jig Bhakta wrote:

>Nope, still getting the error.
Hi Jignesh,
In that case, you better post the complete query. Best is to include
CREATE TABLE statements and INSERT statements with some sample data to
reproduce the error.
Here's a small script that shows that my change does work on my system, so
your error is either caused by something else in your query, by something
strange in your data or by a SQL Server bug. If you post a script that
will reproduce the error, we can find which of these three is the cause.
-- Start of repro script that show the error is corrected
create table table1 (field1 int not null)
create table table2 (field2 varchar(20) not null)
go
insert table1 (field1) select 1 union all select 2
insert table2 (field2) select '1' union all select 'XXXX'
go
print 'Original'
print ''
select * from table1, table2
where table1.field1 = table2.field2
print '--'
go
print ''
print 'Corrected'
print ''
select * from table1, table2
where CAST(table1.field1 AS varchar(10)) = table2.field2
print '--'
go
drop table table1
drop table table2
go
-- Output:
Original
field1 field2
-- --
1 1
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'XXXX' to a column of data type
int.
Corrected
field1 field2
-- --
1 1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Jig,
Try this:
select * from ...
where
case when table1.field1 not like '%[^0-9]%' then cast(table1.field1 as
int) end
= table2.field2
The reason for the error is that a varchar = integer comparison
causes the varchar to be converted to an integer, so XXXX is
getting converted to an integer for the comparison. The CASE
statement causes the query to compare the column field1 with
field2 only if field1 is all digits. Otherwise, it compares NULL, which
won't cause an error.
The best thing to do would be not to store numerical and character
data in the same column! If it makes sense to compare two columns,
but they are not the same type, it's usually a sign that the database design
can be improved.
Steve Kass
Drew University
Jig Bhakta wrote:

>I have a query where I am writing the following statement:
>select * from .....
>where table1.field1 = table2.field2
>...
>...
>...
>field2 is a varchar field and field1 is an int field. Field2 contains the
>data mapping data for field1 and also other unrelated data as character
>strings.
>How do I run this query without getting the error - "Syntax error converting
>the varchar value 'XXXX' to a column of data type int."
>Thanks,
>Jignesh.
>

Converting

I have select like this:
SELECT isnull(table1.structure,'-')as structure1 FROM table1
and I get an error message:
Msg 8115, Level 16, State 6, Line 74
Arithmetic overflow error converting varchar to data type numeric.
Structure column is decimal type.
Why is this not allowed and what is the best solution?
My query in fact is much more complicated and I don't wan't to write a lot o
f corrections
Thank you,
Simon[quote from="Books Online"]
Syntax
ISNULL ( check_expression , replacement_value )
check_expression
Is the expression to be checked for NULL. check_expression can be of any
type.
replacement_value
Is the expression to be returned if check_expression is NULL.
replacement_value must have the same type as check_expresssion.
[/quote]
Your script should look like
[script]
select isnull(cast(@.d as varchar(40)),'-') as structure1 FROM table1
[/script]
Cristian Lefter, SQL Server MVP|||Because '-' is not a numeric value. Did you mean -1, or do you want to have
a dash in the output. Best practice would be to use the UI to do this, or
case table1.structure to a varchar value first. All depends on what your UI
is doing. By changing these to character strings, you might be messing up
what you are going to do with the data once you get it there.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:eYrqV49HFHA.3336@.TK2MSFTNGP10.phx.gbl...
I have select like this:
SELECT isnull(table1.structure,'-')as structure1 FROM table1
and I get an error message:
Msg 8115, Level 16, State 6, Line 74
Arithmetic overflow error converting varchar to data type numeric.
Structure column is decimal type.
Why is this not allowed and what is the best solution?
My query in fact is much more complicated and I don't wan't to write a lot
of corrections
Thank you,
Simon

Convert String to smalldatetime

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Noel Fernandez wrote:

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Try changing Table2.ColumnB to datetime rather than smalldatetime and see if it works.

-Jamie

|||

What type of database are you writing to? I can't replicate this against a SQL Server 2005 db.

If you can't change the datatype of the destination, you might try casting the column to a smalldatetime when you read it from the source.

|||

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Hi Subhash,

Well now I'm confused. First of all you said the error was:

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Then you said it was:

error while converting DB_STR type to DB_DATATIMESTAMP type

Which is it?

-Jamie

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Use this expression in a Derived Column transform:

Code Snippet

(DT_DBTIMESTAMP)(SUBSTRING(DateValue,1,4) + "-" + SUBSTRING(DateValue,5,2) + "-" + SUBSTRING(DateValue,7,2))

The date needs to have the dashes inserted for the cast to work.

|||

Jwelch,

Thanks for your support. That resolved our issue. I was unable to mark this as an answer, however I have marked that as a helpful post.

Regards

Subhash Subramanyam

Convert image datatype to varchar

I have a table Table1 which has a Col called "Msg" datatype image<binary>. Msg alreay has the plain text or RTF text as a image datatype (binary)
If I execute the following query "Select Msg from Table1 where id =3" then this query is returning the following ASCII/Binary data.
Msg = "0x7B5C727466315C616E73695C616E7369637067313235325C64656666305C6465666C616E673130333
37B5C666F6E7474626C7B5C66305C6673776973735C66707271325C6663686172736574302041726961
6C3B7D7B5C66315C6673776973735C66707271325C666368617273657430204D6963726F736F667420"
Can any body tell me how can I convert the above binary data to plain text from my query?
Thanks for any reply.
There is no Conversion of Image to Varchar but you can convert Image to Varbinary with limitations, the text below is from the BOL( books online).
Automatic data type conversion is not supported for the text andimagedata types. You can explicitly converttextdata to character data, andimage data tobinaryorvarbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to anint), SQLServer generates an error message.
Hope this helps.