3 Replies Latest reply: Dec 10, 2012 11:34 AM by Andreas Weiden RSS

    Formula

    Moazam Shareef
      Guyz,

      i wanna to do some calculation here below are the formula.
      30/12=2.5*11.9=2.975
      calculating 30 days divided by 12month below are the perfect result.

      SQL> SELECT 30/12 FROM DUAL;

      30/12
      ---------
      2.5

      here calculating same and the result with multiply by 11.9 11months and 9 days
      SQL> SELECT 30/12*11.9 FROM DUAL;

      30/12*11.9
      ----------
      29.75

      anyone help me out how can i calculate that 11.9 with sysdate/12 so it extract 11.9 11 month and 9 days from sysdate divide by 12 month so i get the proper 29.75 result.

      Regards
        • 1. Re: Formula
          vansul
          30/12=2.5*11.9=2.975


          the above calculation is wrong as it should be
          30/12=2.5
          2.5*11.9=29.75

          you want to devide sysdate with 12 as there is no any division or multiplication with
          date data types.
          you can do addition or subtraction with date data types which will give your the corresponding next and previous dates.

          Actually i could not understood your 11 month and 9 days extraction from sysdate/12
          can you elaborate it.
          • 2. Re: Formula
            Moazam Shareef
            Your are rite vansul...its mah typing mistake or paste mistake :)

            anyhow my scenario is calculation i have some employee vacation calculation based on the below formula in MS access so im converting here in oracle forms 6i.

            formula is
            SQL> select 30/12*11.9 from dual;
            
            30/12*11.9
            ----------
                 29.75
            
            SQL> select 30/12*12 from dual;
            
             30/12*12
            ---------
                   30
            here 30days in 12months
            my requirement is with the joinning date.

            emp_join_date 01-01-2005 (Example)

            in my formula the calculation should be 30/12*12 check with sysdate and number of months from emp_join_date. it should count number of months till today date from the joining date.


            Regards
            • 3. Re: Formula
              Andreas Weiden
              Maybe you can use MONTHS_BETWEEN instead.