Error when using TO_DATE in SQL

Question

I understand the basic TO_DATE usage in Oracle SQL. i google and found some guides to use TO_DATE which is to convert julien date to normal date.

the basic working code is :

SELECT TO_CHAR((TO_DATE(2016365, 'YYYYDDD'))) FROM DUAL)

However, i want to convert date that is in a column which has thousands of them. What i did was :

SELECT TO_CHAR((TO_DATE(PREVDT, 'YYYYDDD'))) FROM DUAL)

The changes is PREVDT because all my julian date is in PREVDT column. However, im getting invalid identifier.....can anyone help me?

I also tried this but no luck:

TO_CHAR((TO_DATE(PREVDT, 'YYYYDDD')))

Passing the converted date through `TO_CHAR()` without specifying a format model converts that date back to a string using your session's NLS_DATE_FORMAT setting - it is no longer a date. That probably isn't what you really want to do - either leave it as a date, or specify the format if you do want it back as a string. — Alex Poole, Jun 15 '16 at 09:02

score 0 · Answer 1 · answered Jun 15 '16 at 08:22

0

You need to provide the table name in which your column PREVDT is present.

select TO_CHAR((TO_DATE(PREVDT, 'YYYYDDD'))) FROM yourTable

answered Jun 15 '16 at 08:22

Rahul Tripathi

score 0 · Answer 2 · answered Jun 15 '16 at 08:23

Oracle DUAL table does not have the column PREVDT. What you want to do is run the query against your table, the one with column PREVDT.

SELECT TO_CHAR((TO_DATE(PREVDT, 'YYYYDDD'))) FROM your_table_name;

The PREVDT values should be in the specified date format otherwise you will get an error.

2 Answers2