Home » SQL & PL/SQL » SQL & PL/SQL » CAST result of MONTHS_BETWEEN in to Integer
icon5.gif  CAST result of MONTHS_BETWEEN in to Integer [message #199669] Wed, 25 October 2006 12:46 Go to next message
netbirds
Messages: 2
Registered: October 2006
Junior Member
Hi All,

I have a Table call CMS_CARD, with fields CARD_NO, EXPIRE_DATE, ....
i need to fetch the number of months between card expire month and SYSDATE of all cards, so i wrote the following query and it fetch the data as i expect,

ex :
select c.card_no, CAST ( MONTHS_BETWEEN( TO_DATE( ('30/'||c.EXPIRY_DATE), 'DD/MM/YY' ) , TO_DATE( SYSDATE, 'DD/MM/YY' ) )AS INTEGER) MONTHS,
TO_DATE( ('30/'||c.EXPIRY_DATE), 'DD/MM/YY' ) EXPIRE_DATE, SYSDATE CURRENT_DATE
from cms_card c;

Then i change the sql query as follows,

select c.card_no, CAST ( MONTHS_BETWEEN( TO_DATE( ('30/'||c.EXPIRY_DATE), 'DD/MM/YY' ) , TO_DATE( SYSDATE, 'DD/MM/YY' ) )AS INTEGER) MONTHS,
TO_DATE( ('30/'||c.EXPIRY_DATE), 'DD/MM/YY' ) EXPIRE_DATE, SYSDATE CURRENT_DATE
from cms_card c where MONTHS = 1;

but then it gives the following SQL exception : "ORA-00904"-"MONTHS": invalid identifier.

can any one suggest where i went wrong in the query ???
env : Oracle 9i, HP-UX

-Thanks
Re: CAST result of MONTHS_BETWEEN in to Integer [message #199671 is a reply to message #199669] Wed, 25 October 2006 13:11 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You should use the full column value in the where clause:

where CAST ( MONTHS_BETWEEN( TO_DATE( ('30/'||c.EXPIRY_DATE), 'DD/MM/YY' ) ,
 TO_DATE( SYSDATE, 'DD/MM/YY' ) )AS INTEGER) = 1;
Previous Topic: Can you tell me how to acess a table or procedure of another user in oracle.
Next Topic: Import records from Access or Excel
Goto Forum:
  


Current Time: Sun Dec 11 00:34:09 CST 2016

Total time taken to generate the page: 0.12332 seconds