DATA type error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DATA type error

Author

Topic

aks
Starting Member

28 Posts

Posted - 2005-03-03 : 14:49:04

Hi,
I have a table with 3 columns as int,datetime & text.
Int date text OUTPUT
4 4
0 2004-09-06 2004-09-06
0 test test
6 6

I'm trying to get the OUTPUT column.
select cast(a.date as varchar),cast(a.text as varchar),
cast(b.text as varchar), cast(a.int as varchar),
cast(b.int as varchar),
case
when a.textfield is null then a.date
else b.date
end,
CASE
when a.textfield is null and a.date is null then a.int
else b.int
end
from secunsec a FULL OUTER JOIN secunsec b
ON a.uid=b.uid
where (a.textfield<>0 or b.textfield <>0)
AND (a.date <>0 or b.date <>0)

But I'm getting error"Syntex error in converting text"test " to a data type int..
What should I do????"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 14:56:29

where (a.textfield<>0 or b.textfield <>0)
s.b.
where (a.textfield<>'0' or b.textfield <>'0')

also
(a.date <>0 or b.date <>0)
s.b.
(a.date <>'19000101' or b.date <>'19000101')

or shiuld they be checking for null?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aks
Starting Member

28 Posts

Posted - 2005-03-03 : 15:02:21

I tried
where (a.textfield<>'0' or b.textfield <>'0')
and
(a.date <>'19000101' or b.date <>'19000101')
Its giving me no output..
I think my query gets confused when a row
int date text OUTPUT LOOKING FOR
0 test test
comes. Query tries to get together 0 & test..Is there any way I can make
"0s" as null values..

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 15:05:47

With thet data you have it won't give any output as you don't have 0 in any of those columns.

What are you trying to do?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aks
Starting Member

28 Posts

Posted - 2005-03-03 : 15:10:51

I have a table with 3 columns as int,datetime & text.
INT DATE TEXT OUTPUT LOOKING FOR
"4" "" "" "4"
"0" "2004-09-06" "" " 2004-09-06"
"0" "" "test" " test"
"6" "" "" "6"
I'm trying to get the last column "OUTPUT LOOKING FOR"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 15:33:07

No you don't "" is not a valid datetime.
You will probably have '19000101' if you inserted an empty string
try

select case when int = 0 then case when date = '19000101' then text else convert(varchar(20),date) else convert(varchar(20),int) end
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aks
Starting Member

28 Posts

Posted - 2005-03-03 : 16:05:06

I made a view
create view varchar as select
(case
when a.val_textfield is null then a.val_date
else convert(varchar(20),a.val_date)
end) as date,
(case
when a.val_textfield is null and a.val_date is null then a.val_int
else convert(varchar(20),a.val_int)
end) as int,
(case
when a.val_date is null and a.val_int=0 then a.val_textfield
else convert(varchar(20),a.val_textfield)
end) as address
from secunsec a FULL OUTER JOIN secunsec b
ON a.uid=b.uid
where (a.uid<>0 or b.uid<>0)
After this I write the query:
select case
when a.address is null and a.int ='0 'then a.date
when a.date is null and a.address is null then a.int
when a.date is null and a.int='0' then a.address
else a.address
end
from varchar a FULL OUTER JOIN varchar b
ON a.uid=b.uid
But Still I got the error"Syntex error converting datetime from character string". I have already converted data types for column in VARCHAR view. I'm wondering why it is giving me again conversion error"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 16:08:45

Why are you using a join at all? Did you look at my response?

The error here is because the case statement needs to return a datatype. As there is a datetime in it and this has the highest precedance then it will convert everything to datetime.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources