operation with date sqlserver

1

Gentlemen, I'm migrating my system from Oracle to SQL Server,

I'm having trouble doing 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 zeroed. Remember that in SQLServer SYSDATE must be replaced with GETDATE()

    
asked by anonymous 11.10.2016 / 16:21

3 answers

-1

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

Regarding the percentage calculation, note that in T-SQL, the / operator has variable behavior, and can be integer 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 the percentage calculation implement something like

  (Cast( dividendo as float) / divisor) as Percentual
    
13.10.2016 / 13:15
0

The corresponding syntax in SQL SERVER for the CAST(((CAST(SYSDATE-EPL.DAT_FABRICA AS INT)) snippet 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
    
11.10.2016 / 22:59
0

Good evening, gentlemen,

Thanks for helping,

I was able to solve the Percentage question in the following way:

       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, –
    
19.10.2016 / 18:28