Convert sql syntaxx to C# syntax

First you determine the algorithm.

Cause does ROUNF(@nbr * @mnt1,@nbr)  make sense? @nbr is 100 for given @ST_NB_DECIMAL_QTY. And the DECIMAL(18,4) does not have 100 digits after the comma..

it will work on Sql you can check it

i need to convert to C# only and not to change anything

it will work on Sql you can check it

I don't doubt that. But what is it intend to do?

The given case converted:

namespace ConsoleCS
{
    using System;

    class Program
    {
        static void Main(string[] args)
        {
            double value = 16.60;
            Console.WriteLine(Math.Round(value, 2));
            Console.WriteLine(Math.Round(value, 2, MidpointRounding.AwayFromZero));
            Console.WriteLine(Math.Round(value, 2, MidpointRounding.ToEven));

            Console.WriteLine("Done.");
            Console.ReadLine();
        }
    }
}

Select allOpen in new window

it is intended to do  truncate for  a value declared as double.

Why don't you truncate in this case? In T-SQL truncating is casting: CAST(@Value * @Factor AS INT) / @Factor. No ROUND, CEILING or FLOOR needed.

what if we have decimals?

Please take some time and analyze the procedure..

You should have started by cleaning up the original peace of code. Good variable naming is everything...

DECLARE @DIGITS INT = 0 ,
        @Value DECIMAL(18, 4) = 16.6543 ,
        @RoundedMultiple DECIMAL(18, 4) ,
        @Result DECIMAL(18, 4) ,
        @Factor BIGINT;

SET @Factor = POWER(10, @DIGITS);
SET @RoundedMultiple = ROUND(@Factor * @Value, @Factor);
IF @RoundedMultiple < 0
    SET @Result = CEILING(@RoundedMultiple) / @Factor;
ELSE
    SET @Result = FLOOR(@RoundedMultiple) / @Factor;

SELECT @Result AS Result ,
       @Value AS [Value] ,
       @DIGITS AS Digits ,
       @Factor AS Factor ,
       @RoundedMultiple AS RoundedMultiple;

Select allOpen in new window

And then you should have run some test with that (assuming it's T-SQL):

DECLARE @Tests TABLE
    (
        TestNo INT IDENTITY(1, 1) ,
        [Value] DECIMAL(18, 4) ,
        Digits INT
    );

INSERT INTO @Tests ( [Value] ,
                     Digits )
VALUES ( 16.60, 1 ) ,
       ( -16.60, 1 ) ,
       ( 16.6543, 1 ) ,
       ( -16.6543, 1 ) ,
       ( 16.5432, 1 ) ,
       ( -16.5432, 1 ) ,
       ( 16.4321, 1 ) ,
       ( -16.4321, 1 ) ,
       ( 16.60, 2 ) ,
       ( -16.60, 2 ) ,
       ( 16.6543, 2 ) ,
       ( -16.6543, 2 ) ,
       ( 16.5432, 2 ) ,
       ( -16.5432, 2 ) ,
       ( 16.4321, 2 ) ,
       ( -16.4321, 2 ) ,
       ( 16.60, 3 ) ,
       ( -16.60, 3 ) ,
       ( 16.6543, 3 ) ,
       ( -16.6543, 3 ) ,
       ( 16.5432, 3 ) ,
       ( -16.5432, 3 ) ,
       ( 16.4321, 3 ) ,
       ( -16.4321, 3 ) ,
       ( 16.60, 5 ) ,
       ( -16.60, 5 ) ,
       ( 16.6543, 5 ) ,
       ( -16.6543, 5 ) ,
       ( 16.5432, 5 ) ,
       ( -16.5432, 5 ) ,
       ( 16.4321, 5 ) ,
       ( -16.4321, 5 );

WITH A
AS ( SELECT T.TestNo ,
            T.Value ,
            T.Digits ,
            POWER(10, T.Digits) AS Factor ,
            ROUND(POWER(10, T.Digits) * T.Value, POWER(10, T.Digits)) AS RoundedMultiple ,
            IIF(ROUND(POWER(10, T.Digits) * T.Value, POWER(10, T.Digits)) < 0 ,
                CEILING(ROUND(POWER(10, T.Digits) * T.Value, POWER(10, T.Digits))) / POWER(10, T.Digits),
                FLOOR(ROUND(POWER(10, T.Digits) * T.Value, POWER(10, T.Digits))) / POWER(10, T.Digits)) AS Result
     FROM   @Tests T )
SELECT A.TestNo ,
       A.Value ,
       A.Digits ,
       A.Factor ,
       A.RoundedMultiple ,
       A.Result ,
       CAST(A.Value * A.Factor AS INT) * 1.0 / A.Factor
FROM   A;

Select allOpen in new window

i am not able to write this code in sql lite studio as per below schreenshot:

(assuming it's T-SQL)

Well, then you need to create a test table, instead of using a table variable. Also the immediate value assignments should be separated.

i tried to use the example that you sent using sql lite  studio.
till now i don't have an answer for what to do in sql lite studio...
thank you

all i need is how to write the below code in SQl lite :

declare @mnt1 decimal(18,4), @mnt_r decimal(18,4), @nbr bigint  
  declare @ST_NB_DECIMAL_QTY INT  
  set @mnt1 = 16.60
    select @ST_NB_DECIMAL_QTY = 2
	  set @nbr = POWER(10 ,@ST_NB_DECIMAL_QTY)  
  Set @mnt_r = round(@nbr * @mnt1,@nbr)  
  IF @mnt_r < 0  
  Set @mnt_r = CEILING(@mnt_r)/ @nbr  
  ELSE  
  Set @mnt_r = FLOOR(@mnt_r)/ @nbr  
  Select  @mnt_r

Select allOpen in new window

since SQl lite does not recognize power, floor and ceiling function
so how can i solve my problem in Sql lite (again )..

thank you

the whole SQL as I see it, is to round a number to a certain precision.
In c# it works with:

mnt_r = Math.Round(mnt1, ST_NB_DECIMAL_QTY);

Select allOpen in new window

that is it.

Though it seems to me that

IF @mnt_r < 0  
  Set @mnt_r = CEILING(@mnt_r)/ @nbr  
  ELSE  
  Set @mnt_r = FLOOR(@mnt_r)/ @nbr  

Select allOpen in new window

is absolutely pointless since CEILING(@mnt_r) or FLOOR(@mnt_r) calculate the same value on mnt_r which at this point is an integer.

@RomanM; Posted that already.. and the point is, that it also seems to be nonsense to me. But I don't have SQL Lite nor time to fully test it.. thus the OP should do this.

OK DONE