Date format in oracle

Posted on 2011-05-12
Last Modified: 2012-06-27

I have a date field in the format of dd-mon-yy(01-MAY-11) and I want to convert it to yyyy-mm(2011-05).
Thanks in advance.
Question by:mrong
    LVL 73

    Accepted Solution

    dates don't have formats,  only strings do

    to convert a date use


    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    If it is a date: to_char('YYYY-MM')
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    is the field date or varchar?

    if it's date:
    TO_CHAR(yourfield, 'YYYY-MM')

    if it's varchar:
    TO_CHAR( TO_DATE(yourfield, 'DD-MON-YY'), 'YYYY-MM')
    LVL 73

    Expert Comment

    if your starting value is actually a string then first, convert the string to a date then convert that date to the final string format


    LVL 73

    Expert Comment

    one last thing,  your starting format has YY

    assuming it's a string then

    I suggest using YYYY,  no need to reinvent the y2k bug
    LVL 73

    Expert Comment


    I appreciate the points, but given 3 essentially simultaneous, identical answers,
     a split would be in order

    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.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now