Re: SQL Question for Conversion
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/10/04
- Next message: Andrew J. Kelly: "Re: Log Reader"
- Previous message: SQL apprentice: "Re: deadlock query"
- In reply to: Dan Guzman: "Re: SQL Question for Conversion"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 9 Sep 2004 20:17:49 -0500
A correction to my previous response:
For example, this query will also return Emp_ID '1040' due to character
comparison rules.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:%23Yp5ZItlEHA.3156@TK2MSFTNGP12.phx.gbl...
> Try enclosing the literal in quotes since you are comparing with a
> varchar. If you specify a numeric integer literal. SQL Server will try to
> convert the varchar column to an integer because integer has a higher data
> type precedence than varchar.
>
> SELECT *
> FROM Employees
> WHERE Emp_ID BETWEEN '104' and '110'
>
> Also, note that BETWEEN might not provide the results you expect. For
> example, this query will also return Emp_ID '1001' due to character
> comparison rules.
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Steve" <anonymous@discussions.microsoft.com> wrote in message
> news:94b001c496d0$86ae7280$a301280a@phx.gbl...
>> Hi,
>> I have a conversion issue in my SQL statement. I have a
>> table Employees(using example here)
>> Emp_ID is Primary Key & has a data type of varchar
>>
>>
>> Emp_ID Emp_Dept
>> 100 Acct
>> 101 Acct
>> 102 FIN
>> 103-A HR
>> 103-B HR
>> 104 Tax
>> 104-A Tax
>> 104-B Tax
>> 105 Acct
>> 106 FIN
>> 107 FIN
>> 108-A Mgmt
>> 109 Mgmt
>> 110 Mgmt
>>
>> Datatype of Emp_ID is varchar. I have to write a select
>> statemnt to get all the values for employees who are in
>> between 104 & 100
>>
>> When I wrtie the following
>>
>>
>> Select * from Employees
>> where Emp_ID between 104 and 110
>>
>> I get the error
>>
>> Syntax error converting the varchar value to a column of
>> data type int.
>>
>> I tried following also
>>
>> Select * from Employees
>> where cast('Emp_ID' as int) between 104 and 110
>>
>> But I got the error
>>
>> Error converting data type varchar to int.
>>
>> Can someone please help me with above
>>
>> Thanks
>>
>> Steve
>
>
- Next message: Andrew J. Kelly: "Re: Log Reader"
- Previous message: SQL apprentice: "Re: deadlock query"
- In reply to: Dan Guzman: "Re: SQL Question for Conversion"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
