change date format 'yyyy/mm/dd' to 'mm-dd-yyyy' in Oracle

Question

I have inserted into a table in Oracle. My implementation without PLSQL would be:

SELECT to_date('1900-01-01','YYYY-MM-DD') + (rownum - 1) AS DT_CAL, 
       rownum AS NUM_JOUR 
FROM   dual 
CONNECT BY to_date('1900-01-01','YYYY-MM-DD') + (rownum - 1) <= 
                                                      to_date('2000-12-31','YYYY-MM-DD')

result is: 05/28/1900, not 1900-05-28. Can you help me understand what the problem is?

Answer 1

to_date() takes your string parameter, matches it to the format you provide in the second parameter, and constructs a date field from it. The date field isn't using the format you provided in the second parameter - in fact it'll be stored using some internal data representation that has no format at all (a number, in all likelihood).

To present a format back out in the results from a date field, you can either:

1. Have the client executing the query set the NLS parameters (at session level) to provide a localized format, with an ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'; statement), or
2. Use to_char(..., 'YYYY-MM-DD') around your existing field to turn the date back into a string formatted the way you want to have it. Where you replace ... with your current column definition in the select.

Approach #1 is already happening, as there'll already be an NLS_DATE_FORMAT set that is producing the current format, but it's with a format you don't want, so if you can control it and change it there, you can do it that way. If you can't and you must have the format a single consistent other way, then #2 could be the way to go.