Post reply

Converting Varchar colum to Money in SQlserver2005

  • October 21, 2010 at 10:34 am

    #225306

    i am parsing the textfile into table so i need to convert the data to Money Field which has 6 digits after decimals

    Ex this is the amt in my textfile 000456300000000 i want to Convert it to money field with 6 digits after decimal

    the result should be 000456300.000000

    so i tried doing this

    select Cast(Substring(Data,304,15)As Money) From Table

    then the result i got is

    456300000000.00 which is wrong

    Can anyone help

    Thanks in advance

  • October 21, 2010 at 12:11 pm

    #1240497

    Your data is missing the decimal point whose sole purpose is to indicate which part is the integer portion and which part is the decimal portion. By convention, the decimal point can only be dropped if there are no decimal places. Your data defies this convention by dropping the decimal point when there are six decimal places. How is SQL supposed to know that you are using a non-standard representation unless you tell it?

    Your options include:

    1) Including the decimal point in your original data. (BEST)

    2) Divide your results by 1,000,000 to reverse the implicit multiplication by 1,000,000 when you drop your decimal point.

    3) Automatically insert the decimal point in the correct position before converting to money.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 2 posts - 1 through 1 (of 1 total)

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