Post reply

need to do math on "nvar" field like this $45.00

  • February 10, 2009 at 7:32 am

    #128125

    I have a filed called "amtdue" that was populated with numbers and a $ sign before it, so they came in a nvarchar. I am hoping to do some kind of math on the field - can someone help me with this?

    My query looks like this:

    UPDATE pharmacy SET pharmacy.HIGHCOSTCLAIM = (SELECT Count(claimfile.keyid) AS CountOfCLAIMSTS FROM claimfile WHERE pharmacy.srvprovid = claimfile.srvprovid and (Convert(Int, Convert(Numeric(25,6),claimfile.amtdue)) >=150 and Convert(Int, Convert(Numeric(25,6),claimfile.amtdue)) <=500)) WHERE (((pharmacy.HIGHCOSTCLAIM) Is Null));

    I get "error converting data type nvarchar to numeric"

    Thanks for any help

  • February 10, 2009 at 7:58 am

    #941231

    You'll need to perform a substring function against the data to get rid of the $ when attempting to execute the query.

    Another way would be to use a computed column based upon the substring, which you could make a numeric data type, this making your query a great deal simpler.


    Shamless self promotion - read my blog http://sirsql.net

  • February 10, 2009 at 8:06 am

    #941242

    Thank you - but I think I got something simpler, I took out the other conversion stuff and "cast as money" that field (amtdue)and it seemed to work

  • February 10, 2009 at 8:18 am

    #941258

    Nice, didn't even know that you could do that. Will remember that one for the future.

    Thanks


    Shamless self promotion - read my blog http://sirsql.net

  • February 10, 2009 at 10:49 am

    #941461

    Hi

    Its not a good design to have Currency symbol as part of Amount Field. Always separate these into two different columns.

    Thanks

    Vijaya Kadiyala

    http://dotnetvj.blogspot.com

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply