This discussion is archived
7 Replies Latest reply: Apr 10, 2013 12:55 AM by indra budiantho RSS

subtracting month and day

968357 Newbie
Currently Being Moderated
hello i have to convert my date i. e. in dd/mm/yyyy format from 04/10/2013 to 10/04/2013.


pl temm me how to do it.....

With regards
vishal agrawal
  • 1. Re: subtracting month and day
    O.Developer Journeyer
    Currently Being Moderated
    Hi

    If you just want to dispaly format, then try

    Select To_CHar(Datefile, 'DD-MM-YYYY') , TO_CHAR(DATEFILED1, 'MM-DD-YYYY') FROM TABLE NAME.


    But your Titile and your question is not cleare..

    965354 wrote:
    hello i have to convert my date i. e. in dd/mm/yyyy format from 04/10/2013 to 10/04/2013.


    pl temm me how to do it.....

    With regards
    vishal agrawal
  • 2. Re: subtracting month and day
    968357 Newbie
    Currently Being Moderated
    i have to convert date 04/10/2013 to 10/04/2013 explicitly in my database . dates are in dd/mm/yyyy.
    because of some mistakes it happens that instead of 10/04/2013 we save it 04/10/2013.
  • 3. Re: subtracting month and day
    1002147 Newbie
    Currently Being Moderated
    Hi 965354,

    By default, Oracle stores the date field as mm/dd/yyyy format only in Database.
    So, If u want to convert date for displaying it in UI/forms, then use TO_DATE(date_column_item,'dd/mm/rrrr').

    Hope it is useful.
  • 4. Re: subtracting month and day
    Marwim Expert
    Currently Being Moderated
    Oracle stores the date field as mm/dd/yyyy format only in Database
    No, mm/dd/yyyy is a display format. The internal storage has no formatting except the ordering of bytes used to store the information.

    So when the date is stored in a DATE column, then the display format is defined by the session settings
    SELECT * FROM nls_session_parameters
    WHERE parameter = 'NLS_DATE_FORMAT'
    or by passing a format mask in TO_CHAR.

    In Forms you can define a format mask on a date field in the item properties.

    A completly different thing would be if the date is stored in a VARCHAR2 column. While it is a very bad practice that can lead to a lot of corrupt data we can find it much too often.
    In this case you could do
    TO_CHAR(TO_DATE(varchar_date_column,'mm/dd/yyyy'),'dd/mm/yyyy')
    But don't do this, when the column is defined as DATE!!

    Regards
    Marcus
  • 5. Re: subtracting month and day
    O.Developer Journeyer
    Currently Being Moderated
    Hi

    Now I understood your point.

    My advice is do not make any change on the database , let it as it is.

    You apply all your restriction and validation in your front end forms. This will solve your doubt


    965354 wrote:
    i have to convert date 04/10/2013 to 10/04/2013 explicitly in my database . dates are in dd/mm/yyyy.
    because of some mistakes it happens that instead of 10/04/2013 we save it 04/10/2013.
  • 6. Re: subtracting month and day
    968357 Newbie
    Currently Being Moderated
    thanks u all.


    DATEFILE+NUMTOYMINTERVAL(-6,'MONTH')+6 SOLVED MY PROBLEM
  • 7. Re: subtracting month and day
    indra budiantho Expert
    Currently Being Moderated
    MARK IT AS CORRECT THEN

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points