Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Lightbulb Unanswered: DDD date format in oracle

    Hi ,
    Please could someone suggest me a way to convert date which is in the DDD format in my table back to dd/mm/yyyy format in oracle?
    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    use to_date(col,'ddd')?
    want to change it's display after that - to_char( to_date(col,'ddd'), 'dd/mm/yyyy')

  3. #3
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    Hi ,
    Thanks for the reply but it does not work : I get the error


    The following error has occurred:

    ORA-01830: date format picture ends before converting entire input string


    Is there any other way to do this ????/

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    post the actual sql that gave the error

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If all you have is the julian day of the year, then when you convert back to a date, it will assume the current year. See the example that follow.

    SQL> select to_char(sysdate,'DDD') from dual;

    TO_
    ---
    196

    SQL> select to_date('196','ddd') from dual;

    TO_DATE('
    ---------
    14-JUL-04

    if what you have in the table is a character string like 'DDDYYYY' then use the following

    select to_date('1962003','dddyyyy') from dual;
    Last edited by beilstwh; 07-14-04 at 15:30.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    Just a small thing, but there's no such thing as a "julian day of the year". The Julian calendar is a whole seperate thing, so a julian day is a julian day. Day of year (DDD in oracle) is the day of the year, no relation to Julian at all.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    True, but Old habits die hard. In my old mainframe days, the day of the year was referred to as the julian day.

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    Ooh, the insults to mainframers are running through my head. I think I'd better just keep them to myself.

  9. #9
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    Thanks shoblock and beilstwh - tried the following sql and it works !

    select to_date(to_char(thedatecol),'ddd') from mytable.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Exclamation

    if the "thedatecol" column, is already a date use it directly. An oracle date column always contains the entire date. If you stored a day-of-the-year into a date column, the date will already contain month, day, year, hour, minute, and second information and can be accessed directly. To test it, try the following select.

    select to_char(thedatecol,'DD-MON-YYYY HH:MI.SSAM') FROM MYTABLE;



    Quote Originally Posted by sching
    Thanks shoblock and beilstwh - tried the following sql and it works !

    select to_date(to_char(thedatecol),'ddd') from mytable.
    Last edited by beilstwh; 07-15-04 at 16:39.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    Thanks a lot!
    But since the column is a number column - I needed to convert it to char.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •