To Convert date column into 'dd/mm/yyyy' format without using to_char function

Question

while selecting a date column in oracle, I want to Convert that date column into dd/mm/yyyy with out using a to_char function. Please suggest is there any way to get the required output.

My Date Format is DD-MMM-YY and I want to convert into dd/mm/yyyy without using to_char function

What data type is that column? A `date` column does not have "a format". Any format you _see_ is applied by whatever SQL client you are using. — , Apr 14 '16 at 09:50
Why the restriction of not using the right tool for the job? Is it a hacker's quiz or something? — Álvaro González, Apr 14 '16 at 09:51

score 0 · Answer 1 · answered Apr 14 '16 at 10:03

Of course, TO_CHAR() is simply the right tool for the job. But you can always change the session variable that controls the default output format and restore it later:

ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY';
SELECT CAST(SYSDATE AS VARCHAR2(10 CHAR)) AS FORMATED_DATE
FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY';

The CAST bit is just a precaution against client tools handling the result as date and potentially changing format.

To Convert date column into 'dd/mm/yyyy' format without using to_char function

1 Answers1