Printable View
Hello,
Pls am using TO_DATE function in my select stt. But its returning the wrong dates for me. In the database a text field contains 20021231. i want to convert it to date format and am using this format
TO_DATE(ind_val,'dd/mm/yyyy')
I want the format above to return 31/12/2001 from 20021231 but its returning 20-feb-31.
Pls anyone have and idea how i can return exactly dd/mm/yyyy from 20021231.
you need to specify the format of the data in your column
TO_DATE(ind_val,'yyyymmdd')
and set your nls_date_format to
alter session set nls_date_format = 'DD/MM/YYYY' to display it how you want it
SQL> select to_char(to_date('20021231','yyyymmdd'),'dd/mm/yyyy') from dual;
TO_CHAR(TO
----------
31/12/2002
thanx. It worked.
A word to the wise:
If you're experimenting, testing etc in SQL*Plus then setting NLS_DATE_FORMAT, as suggested by davey, is ideal.
If you're writing an "application", the only sure-fire way to get it to always work, is to fully specify the date formats as engiri did.