0

I have two Oracle columns (in a VIEW, not a table) - DateOfBirth and MembershipDate. I am trying to create a new column that will show the age of the person on the date that they took up the membership.


Sample columns:
DateOfBirth: 01/05/1975
MembershipDate: 01/09/2015
AgeWhenBecameMemb: 40

How do I get the third column to get calculated on the fly, please?


I'm looking to implement it using something like this:

SELECT FLOOR(MONTHS_BETWEEN(DATE '2012-01-01', DATE '2010-10-10')/12) AS AgeWhenBecameMemb from DUAL

Thanks!

Community
  • 1
  • 1
Mr.L
  • 1
  • 2
  • Can you post the error you're getting ? The formula seems to work fine for me ? (might be your date format when you use `DATE '2012-01-01'` ... try putting the column names in there, or using `to_date()` function – Ditto Sep 18 '15 at 14:45
  • Yes, the above code works. However, my date values are in DD/MM/YYYY - this format is not accepted. :( – Mr.L Sep 18 '15 at 14:47
  • L : .. ok ... so are you going to let us know what is accepted, and what you need? or do we have to keep guessing? O.o You might want to reconsider re-writing your question .. it clearly asks "how do I get the third column to get calculated on the fly" .. that's been answered .. however, I get the impression that's not the question you wanted to answer ... O.o – Ditto Sep 18 '15 at 14:49

3 Answers3

0

which version of Oracle?

In Oracle 11 or later, you have virtual columns: check this out: https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:676611400346196844 ...

prior to that, you could consider a view, or materialized view - depending on other needs you have.

Ditto
  • 3,256
  • 1
  • 14
  • 28
  • Oracle 12.1. I am creating a view here, trying to create a new column (AgeWhenBecamememb) in this view, using the DateOfBirth and MembershipDate values. I just cant get the calculation to work – Mr.L Sep 18 '15 at 14:35
0

Use the TO_DATE() function to supply your own date format. For example:

select to_date('01/05/1975','dd/mm/yyyy') as DateOfBirth
     , to_date('01/09/2015','dd/mm/yyyy') as MembershipDate
     , FLOOR(MONTHS_BETWEEN(to_date('01/09/2015','dd/mm/yyyy'), to_date('01/05/1975','dd/mm/yyyy'))/12) AS AgeWhenBecameMemb
from dual;

For your specific data:

select FLOOR(MONTHS_BETWEEN(to_date(MembershipDate,'dd/mm/yyyy'), to_date(DateOfBirth,'dd/mm/yyyy'))/12) AS AgeWhenBecameMemb
from dual;

The syntax you're trying to use (ANSI date literals) is described here: http://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062

Per the documentation:

The ANSI date literal contains no time portion, and must be specified in the format 'YYYY-MM-DD'. Alternatively you can specify an Oracle date value, as in the following example:
TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')

Mark Leiber
  • 3,118
  • 2
  • 13
  • 22
0

thanks for everybody's input. Following taking on your responses, i've got this column to work, like this -

(select floor(TO_CHAR(MembershipDate, 'yyyy-mm-dd'), TO_CHAR(DateOfBirth, 'yyyy-mm-dd')) /12) AS AGE_OF_CONSUMER from dual) AS AgeWhenBecameMemb

The fields in use for the comparision were in date format already.

Thank you.

Mr.L
  • 1
  • 2