len function issue

  • 12 years ago


    I really don't know why, every time I try to execute the following statement:
                    select convert(decimal(10,len(AMOUNT)),AMOUNT) from CALIB_ANALYTES

    I receive an error message: "Line 1: Incorrect syntax near 'len'."

    If instead of Len(AMOUNT) I put a number, it works fine.

    Please advise me.

    Thank you,

  • 12 years ago
    I dont know what db you are using but I suspect that you cannot supply an Expression to decimal's precision and scale parameters. I know you cant with Sybase or SQL Server.


  • 12 years ago
    Yes, I'm using SQL Server.
    Are you very sure that I cannot a function instead of a certain value for parameters in decimal? If this is so, how else could I solve this?

    Thank you,
  • 12 years ago

    Yes it is illegal in SQL Server.

    If you are trying to turn integers into decimals then

           select AMOUNT / Power( 10 , len(AMOUNT) )

    will return .12345 where AMOUNT = 12345

    It really depends on what datatype AMOUNT is and what you are trying to do to it.



  • 12 years ago

    You're right, I'm sorry.

    The AMOUNT field is varchar and contains values like 4.0 or 12.20.
         What I'm actually trying is to format another filed(numeric this time, containing a calculated value based on AMOUNT), from another table to have the same number of decimals like this one. I solved that issue using round instead (not a very good solution, but at list accepts expresions as parametes) and a very complicated nested cases.

    Thanks anyway for your responses

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Theory is when you know something, but it doesn't work. Practice is when something works, but you don't know why. Programmers combine theory and practice: Nothing works and they don't know why.”