Thursday, March 22, 2012
Converting a field from int to varchar during query
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.
>
Sunday, March 11, 2012
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