1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Converting AUG-09 to date in Oracle-SQL

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Aug 13, 2009.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello My Friends,

    Can some one help me to show me how to convert MMM-YY like AUG-09 to date in ORACLE-SQL?
    I need this conversion to compare this result with a date column field like:

    and ccx_ERSDispatch.CreateTime BETWEEN :StartingMonth and :EndingMonth

    assuming the value of StartingMonth is AUG-08 and the vlaue of EndingMonth is AUG-09.


    With Respect,
    Farshad Javadi
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    you have to use to_date my friend. But remember that using to_date will take the day as the 1st date of the month.

    Code (SQL):

    SQL> SELECT to_date('AUG-09','MON-YY') FROM dual;

    TO_DATE('
    ---------
    01-AUG-09

    SQL>
    Also you may find the function months_between useful.

    Code (SQL):

    SQL> SELECT months_between(to_date('AUG-08','MON-YY'),to_date('AUG-09','MON-YY')) FROM dual;

    MONTHS_BETWEEN(TO_DATE('AUG-08','MON-YY'),TO_DATE('AUG-09','MON-YY'))
    ---------------------------------------------------------------------
                                                                      -12

    SQL>


     
     
  3. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Dear sameer,

    Thanks. Your solution worked for me. I really appreciate it.



    With Respect,
    Farshad Javadi