How to convert varchar to money on sql insert

Posted on 2010-01-06
Last Modified: 2012-08-14
I need to convert varchar to money data type when doing a SQL insert. I'm using sql server 2000. Below is my insert statement.
(custno, sodate, ordate, disc, taxrate, tax, ordamt, authamt, ponum, salesmn, sostat, sono, defloc, finalized, adddatetime) VALUES     ('#qrymast.custno#','#qrymast.sodate#','#qrymast.ordate#','#qrymast.disc#','0','0','#qrymast.ordamt#','0','#qrymast.ponum#','#qrymast.salesmn#','#arguments.status#','#qrymast.sono#','#defloc#','#finalized#','#qrymast.adddatetime#')

The Insert fails on taxrate, tax, ordamt. I have tried the CONVERT function but have not been able to get it to work, and have left my attempts out of this post only to keep it brief.
Question by:dawes4000
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    declare @x varchar, @y money
    select @y =123900.00
    select @x = convert( varchar(100), @y)

    Accepted Solution

    No need to convert, only have to remove the quotes around taxrate, tax, and ordamt, otherwise they were treated as char. This explains why convert never worked.
    LVL 51

    Expert Comment


    If you were using cfqueryparam you wouldn't have to worry about this kind of problem ;-)
    LVL 51

    Expert Comment

    ... I think I submitted that last comment via "SUBMIT" and not "OBJECT".   But in case I didn't, to any mods:  No, I don't have any objection :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now