This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Dec 12, 2012 3:56 AM by Rahul_India RSS

Date format

979205 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    select to_char(to_date('2012-12-11','YYYY-MM-DD'),'DD-MM-YYYY') from dual;
  • 3. Re: Date format
    AlbertoFaenza Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    First describe the table and see what is the data type of the columns

    SQL > DESC <table_name>
  • 9. Re: Date format
    979205 Newbie
    Currently Being Moderated
    can u be more specific...?
  • 10. Re: Date format
    Rahul_India Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thank you for the patience!
    the manufacturing_date is varchar2 type
  • 13. Re: Date format
    LPS Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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