If you’re working with DATE fields in Oracle, and select this data, you’ll see the dates in the default output format.
Depending on your location, the default date format could be DD-MON-YYYY or something similar.
If you want to display dates in a different format, use the Oracle TO_DATE function.
For example if you want to display the date 10th Jan 2017 in a DD/MM/YYYY format, use the function like this:
SELECT TO_DATE('10-JAN-2017', 'DD/MM/YYYY') AS date_testFROM DUAL;
You can replace the ’10-JAN-2017′ part with a column that has the date value as well.
SELECT TO_DATE(start_date, 'DD/MM/YYYY') AS date_testFROM DUAL;
Or output in a different format:
SELECT TO_DATE('10-JAN-2017', 'YYYY_MON_DD DAY) AS date_testFROM DUAL;