Extract year information from date string  in oracle

Posted on 2009-02-13
Last Modified: 2012-05-06
I have a field say dat_val in the table of type char. The dates are stored in two different formats as of now. ie yyyy-mm-dd and mm-dd-yyyy. I am having problem writing a function to extract the Year from this string field. The function should be able to extract year anywhere in the string along with the above two formats, say mm-yyyy-dd!

Question by:crgary_tx
    1 Comment
    LVL 47

    Accepted Solution

    v_pos   INTEGER;
      v_pos := INSTR(p_dt,'-');
      IF      v_pos = 5   THEN RETURN (substr(p_dt,1,4)
      ELSE RETURN (substr(p_dt,7,4)
      END IF;
    show err

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now