operation with data sqlserver

Question

Gentlemen, I am migrating my Oracle system to SQL Server,

I’m having trouble making the following operation

SELECT
    CASE WHEN (EPL.DAT_VENCIMEN - EPL.DAT_FABRICA) > 0
      THEN CAST(((CAST(SYSDATE-EPL.DAT_FABRICA AS INT)) /
        (CAST(EPL.DAT_VENCIMEN-EPL.DAT_FABRICA AS INT)) * 100) AS INT)
      ELSE 0
    END AS PERCENTUAL_P,
    epl.*
FROM emp_produto_lotacao EPL

this query returns the values correctly in Oracle. However in Sqlserver the values are reset. Remember that in Sqlserver the SYSDATE should be replaced by GETDATE()

Answer 1

Good afternoon gentlemen,

Thank you for helping,

I was able to solve the Percent question as follows:

       CASE WHEN (cast(EPL.DAT_VENCIMEN - EPL.DAT_FABRICA as float)) > 0
            THEN CAST((( CAST(GETDATE() AS FLOAT) - CAST(EPL.DAT_FABRICA AS FLOAT))
                 /  (CAST(EPL.DAT_VENCIMEN AS FLOAT) - CAST(EPL.DAT_FABRICA AS FLOAT)) * 100) AS  INT) 
            ELSE 0 
       END AS PERCENTUAL_P, –

Answer 2

The corresponding syntax in SQL SERVER for the stretch CAST(((CAST(SYSDATE-EPL.DAT_FABRICA AS INT)) is DATEDIFF(day, getdate() , EPL.DAT_FABRICA)

Your query would look like this.

SELECT
    CASE WHEN (DATEDIFF(day, EPL.DAT_VENCIMEN , EPL.DAT_FABRICA)) > 0
      THEN (DATEDIFF(day, getdate(), EPL.DAT_FABRICA)) 
      / (DATEDIFF(day, EPL.DAT_VENCIMEN , EPL.DAT_FABRICA) * 100)
      ELSE 0
    END AS PERCENTUAL_P,
    epl.*
FROM emp_produto_lotacao EPL

Answer 3

Ivan, to calculate the difference between dates, use the function Datediff.

Regarding the percentage calculation, aware that, in T-SQL, the operator / It has variable behavior, and can be entire division or real division (floating point), depending on the characteristics of the operands. For example:

  1 / 2 = 0
  1.0 / 2 = 0.5

So I suggest that in calculating the percentage you implement something like

  (Cast( dividendo as float) / divisor) as Percentual