operation with date sqlserver

Navigation
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
I made a SplashScreen it loads and will not go to the first activity what should I do? [closed] Convert number of the day of the week into String of the day