Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Victoria, Canada
    Posts
    3

    Unanswered: Can't use nvarchar in BETWEEN condition

    Greetings all,

    I am trying to do an SQL query with BETWEEN in my where condition. Problem is the values I am checking are nvarchar since they also have letters in them. This results in a query that produces nothing. Here is my query:

    CREATE PROCEDURE dbo.qryComplete
    (
    @From nvarchar(50),
    @To nvarchar(50)
    )

    AS SELECT dbo.[tbl Object].[Object ID],
    dbo.[tbl Object].[Object Name], dbo.[tbl Object].[Object Type],
    dbo.[tbl Object].Category, dbo.[tbl Object].Subcategory,
    dbo.[tbl Object].[Group], dbo.[tbl Object].[Accession Number],
    dbo.[tbl Object].[Physical Description],
    dbo.[tbl Object].[Acquisition Method],
    dbo.[tbl Object].[Acquisition Date],
    dbo.[tbl Object].[Acquisition Source], dbo.[tbl Object].Status,
    dbo.[tbl Object].[Number of Parts or Components],
    dbo.[tbl Object].[Description of Parts or Components],
    dbo.[tbl Object].Title, dbo.[tbl People].[Persons Name],
    dbo.[tbl People].Address, dbo.[tbl People].City, dbo.[tbl People].Province, dbo.[tbl People].Country,
    dbo.[tbl People].[Postal Code], dbo.[tbl People].Biography,
    dbo.[tbl People].Status AS Dead, dbo.[tbl People].Phone
    FROM dbo.[tbl Object] LEFT OUTER JOIN
    dbo.[tbl People] ON [tbl Object].[Acquisition Source] = [tbl People].[Persons Name]
    WHERE (dbo.[tbl Object].[Accession Number] BETWEEN @From AND @To) AND (dbo.[tbl Object].Status LIKE 'A%' OR
    dbo.[tbl Object].Status LIKE 'B%' OR
    dbo.[tbl Object].Status IS NULL)
    ORDER BY dbo.[tbl Object].[Accession Number]
    GO


    If I replace Accession Number with Object ID in the WHERE condition, this query works fine. With the accession number however it returns no results. I have also tried using convert and cast functions to try to convert the values to numeric but I get an error saying it can't convert to type numeric. I've also tried replacing the BETWEEN with comparison operators (>=) but it still doesn't work. Some examples of the Accession numbers we are using:
    CF1968.5
    CF1968.7A-E
    CS1986.2.2

    Any suggestions?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post @FROM and @To as well and then show us what you think the result set should be
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Check out this example, and let me know what you think

    Code:
    USE Northwind
    GO
    sp_help Orders
    GO
    DECLARE @From char(1), @To char(1)
    SELECT @From = 'A', @To = 'B'
    SELECT * FROM Orders WHERE ShipName BETWEEN @From AND @To
    GO
    But, really BETWEEN with chars?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2003
    Location
    Victoria, Canada
    Posts
    3

    Talking

    Yes I agree that doing it with nvarchars is not a very good way of doing it. What I am working with is a hand-me down of a hand-me down .

    As a bit more background this file is an ADP accessing a MS SQL Server database. It was originally done in Access but we converted it so that we can eventually use it on the web. I've gotten rid of most of the conversion issues including getting the forms to work properly and all I have left is these pesky queries/reports. Apparently access had no problem using BETWEEN with two nvarchar values but MS SQL seems to be a bit pickier.

    Examples of @From and @To I've been trying to use are:
    @From : CF1968.5
    @To : CF1968.20

    I would expect this to return records with the following Accession numbers:

    Awwww crap!

    Okay now I feel really stupid. When I did the sort I noticed that CF1968.20 actually comes before CF1968.5. It puts it in alphabetical order after all, not numerical. So when I redid the query between CF1968.5 and CF1968.505 the query returned several values.

    Well thanks a lot for your help. Your requests were instrumental in solving my problem for me.

    Have a great day.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    glad I could help....

    Oh, and I feel for you...just got 1 dumped on me...luckily they don't want to convert...

    touching any code in this thing would cause MAJOR explosions...

    Keeping it afloat...that's another matter...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •