i am following learn sql the hard way tutorial, in tutorial tutor suggest to use sqllite 3 but im using MSSQL 2008 and i have a problem, tutor ask from me to find something using AND or OR operators, her is the query i wrote;

select * from pet where name like 'Flu%' and age = 1000

but i receive:

Msg 206, Level 16, State 2, Line 13 Operand type clash: text is incompatible with smallint

i try this one

select * from pet where name like 'Flu%' and age = '1000'

i receive :

Msg 402, Level 16, State 1, Line 13 The data types text and varchar are incompatible in the equal to operator.

and when i convert age to varchar it works;

select * from pet where name like 'Flu%' and convert (varchar, age) = 1000
select * from pet where name like 'Flu%' and convert (varchar, age) = '1000'

it returns the correct answer

Here is the table design (as tutor wants); id=int, name=text, breed=text, age=text, dead=int

why it doesnt work before converting to varchar?

Looking forward to your reply.



You got problem because sqllite and sql server datatype are different.

In the specific, you use a text datatype. This is bad, in sql server, for many reasons:

So, I suggest you to build your table with appropriate datatypes. In your case name must be a varchar and age an integer (if it is ok).

Also you must not learn starting on a book for a specific rdbms and use another one, or you will get a lot of problems like this.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.