date format manipulation error

[joeroe3380]

HI,

I want to extarct a date field from the table and transform into a different date format("yyddd")..say if the input date is 20010201..outut date format should be 01032.
i have tried with following queries but have encountered the following errors..


sql>describe tabledate

DATA_DATE NOT NULL DATE


SQL> select data_date from tabledate where rownum<20;

DATA_DATE
---------
03-JAN-03
31-DEC-02
31-DEC-02
31-DEC-02
31-DEC-02
03-JAN-03
31-DEC-02
31-DEC-02
03-JAN-03
31-DEC-02
03-JAN-03

DATA_DT
---------
03-JAN-03
03-JAN-03
31-DEC-02
03-JAN-03
03-JAN-03
03-JAN-03
03-JAN-03
31-DEC-02

19 rows selected.

SQL> select to_date(data_date,'yyyymmdd') data_dt from tabledate;
select to_date(data_date,'yyyymmdd') data_date from tabledate
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select to_number(to_date(data_date,'yyyymmdd')) data_dt from tabledate;
select to_number(to_date(data_date,'yyyymmdd')) data_dt from tabledate
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select to_date(to_char(data_date,'yyyymmdd'),'yyddd') data_new from tabledate where rownum<25;
select to_date(to_char(data_date,'yyyymmdd'),'yyddd') data_new from tabledate where rownum<25
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

please advice me on how to go about getting the required format..and also the reason for the above encountered errors!

Thanks in advance for your time nad help!!

regards,
suzzan'e

 

[lewisp]

Youre nearly there with to_char.

SELECT To_Char(data_date,'yyddd') data_new
FROM   tabledate
WHERE  rownum < 25;