Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Unanswered: CAST Numeric vs CAST int

    Hi guys,

    I have a weird issue that I am trying to figure out with no luck.

    I have a table that contains a varchar(10) field that contains values such as 'MAR10', 'FEB09' etc. Basically it is a 3 char month plus a two digit year.

    I have a stored proc that queries this table and uses the right function to grab just the year portion of the field and converts it into a numeric.

    This field is then used in the order by clause.

    The weird issue is this....

    When I use

    Code:
    Cast(Right(FM.FutureMaturity,2) as numeric) as [FutureMaturityYear]
    it fails saying "Error converting data type varchar to numeric."

    If I change the line to
    Code:
    Cast(Right(FM.FutureMaturity,2) as int) as [FutureMaturityYear]
    The query runs without a problem.

    I can't figure out why it would work for int by not for the more generic numeric. What am I missing?

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    you probably have one or more rows in the table where the data in your column does not match the pattern you have described.
    A value of '' does that for me. Interestingly this value casts to an integer value of zero, but won't cast to a numeric, giving the same message you have described...

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    That's what I originally thought myself so I did some investigation into the data that was being queried and there does not appear to be any problems there.

    I then thought it might be due to the ORDER by clause but i have dismissed that as well as that should only happen after the base data has been grabbed (unlike a WHERE clause).

    But I think I just figured out what is actually causing the issue.

    The query I am performing spans multiple tables with criteria for the where clause spanning across them. I think the query is starting with a query across the FutureMaturity data table and then linking to the main query. So when it is doing the CAST it is not being performed across the subset of data I thought it was it is actually going across a larger cross section of data.

    Now all I need to do is remember how to get a query plan for a stored proc and I will be able to confirm my suspicions.

Posting Permissions

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