This discussion is archived
3 Replies Latest reply: Dec 10, 2012 9:34 AM by Andreas Weiden RSS

Formula

Moazam Shareef Explorer
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Maybe you can use MONTHS_BETWEEN instead.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points