1 2 Previous Next 22 Replies Latest reply: Dec 12, 2012 5:56 AM by Rahul_India RSS

    Date format

    979205
      How can I change the date format from 'YYYY-MM-DD' to 'DD-MM-YYYY'?
      to_char / to_date r not helping...
        • 1. Re: Date format
          Veejays.User10302525-Oracle
          select to_char(sysdate,'dd/mm/yyyy'),to_char(sysdate,'yyyy/mm/dd'),to_char(sysdate,'dd/mm') from dual
          are you using some tool?, can you try above query in sqlplus and tell us if the date is not in respective format
          • 2. Re: Date format
            LPS
            select to_char(to_date('2012-12-11','YYYY-MM-DD'),'DD-MM-YYYY') from dual;
            • 3. Re: Date format
              AlbertoFaenza
              976202 wrote:
              How can I change the date format from 'YYYY-MM-DD' to 'DD-MM-YYYY'?
              to_char / to_date r not helping...
              Hi,

              welcome to the forum.

              Please read SQL and PL/SQL FAQ

              Why don't you try to show us what you are doing and what is not helping?

              Additionally when you put some code or output please enclose it between two lines starting with {noformat}
              {noformat}
              
              i.e.:
              {noformat}
              {noformat}
              SELECT ...
              {noformat}
              {noformat}
              
              Regards.
              Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              • 4. Re: Date format
                979205
                select to_char(to_date(t.MANUFACTURING_DATE,'YYYY-MM-DD'),'DD-MM-YYYY')
                from lots_book t

                its said- ORA-01830:date format picture ends before converting entire input string
                • 5. Re: Date format
                  LPS
                  Here what is the data type of MANUFACTURING_DATE

                  if its date no need to use to_date,since you have already mentioned that its in 'YYYY-MM-DD', or else mention what is the data type of MANUFACTURING_DATE


                  select to_char(t.MANUFACTURING_DATE,'DD-MM-YYYY')
                  from lots_book t
                  • 6. Re: Date format
                    AlbertoFaenza
                    976202 wrote:
                    select to_char(to_date(t.MANUFACTURING_DATE,'YYYY-MM-DD'),'DD-MM-YYYY')
                    from lots_book t

                    its said- ORA-01830:date format picture ends before converting entire input string
                    It looks you totally ignored my post.

                    How could we know which data type you are dealing with?
                    Have you read SQL and PL/SQL FAQ

                    Additionally when you put some code or output please enclose it between two lines starting with {noformat}
                    {noformat}
                    
                    Anyway this error is coming when you try to convert a string to date without having specified the whole string format.
                    i.e.:
                    SELECT TO_DATE ('2012-12-11 15:00:00', 'YYYY-MM-DD') FROM DUAL;

                    Error at line 1
                    ORA-01830: date format picture ends before converting entire input string
                    What a bad habit to have a date in a VARCHAR2 column!!
                    
                    Regards.
                    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                    • 7. Re: Date format
                      979205
                      hi Al
                      I didnt mean to ignore, I didnt understand...
                      Im new here...
                      how do I know what type is it? it appers like that-
                      2010/03/02 00:00:00
                      • 8. Re: Date format
                        LPS
                        First describe the table and see what is the data type of the columns

                        SQL > DESC <table_name>
                        • 9. Re: Date format
                          979205
                          can u be more specific...?
                          • 10. Re: Date format
                            Rahul_India
                            Alberto Faenza wrote:
                            976202 wrote:> orums?</a>
                            Additionally when you put some code or output please enclose it between two lines starting with {noformat}
                            What a bad habit to have a date in a VARCHAR2 column!!
                            
                            Regards.
                            Al
                            I know it is a bad habit to store date in varchar2.But why.Tom kyte has iterated this statement in number of places.Its also bad to use YY. Explanation needed
                            • 11. Re: Date format
                              Marwim
                              can u be more specific...?
                              LPS asked
                              what is the data type of MANUFACTURING_DATE
                              So open SQL*PLUS, type DESC followed by the name of the table and tell us the data type of the column manufacturing_date. Is it DATE or VARCHAR2.

                              Regards
                              Marcus
                              • 12. Re: Date format
                                979205
                                thank you for the patience!
                                the manufacturing_date is varchar2 type
                                • 13. Re: Date format
                                  LPS
                                  select to_date('2012/12/10 00:00:00','RRRR/MM/DD HH24:MI:SS') from dual;

                                  USE LIKE THIS

                                  select to_date(manufacturing_date ,'RRRR/MM/DD HH24:MI:SS') from <TABLE_NAME>;
                                  • 14. Re: Date format
                                    jeneesh
                                    976202 wrote:
                                    thank you for the patience!
                                    the manufacturing_date is varchar2 type
                                    Please post the output of
                                    select manufacturing_date  from your_table;
                                    1 2 Previous Next