If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I'm afraid you are mixing up the format of your output (what you would like to have on screen), and the format used in the to_date function to indicate how the character value is stored.

You have two solutions :

1. TO_CHAR(TO_DATE(START_DATE_ACTIVE,'YYYY-MM-DD'),'DD-MM-YYYY')

2. Change the NLS_DATE_FORMAT of your session. If you are using windows, and if I'm not mistaking, you will have to operate this change in the registry. I don't think the following will work on Windows (ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'), but I need to check this out.

So, my guess is you should be able to change the NLS_DATE_FORMAT in your session without any problems. If you see that your ALTER SESSION... has no impact, than check out if there is any value set in the registry (LOCAL_MACHINE/SOFTWARE/ORACLE...)