7 Replies Latest reply: Apr 10, 2013 2:55 AM by indra budiantho RSS

    subtracting month and day

    968357
      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
          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
            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
              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
                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
                  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
                    thanks u all.


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