Oracle SYSDATE format Change

Question

I am trying to change a format of Oracle SYSDATE so that it uses a specific format when running a PL/SQL procedure. This format is really unnecessary but I am update an old table and need to maintain this format due to some integrations. My problem is I am not able to replicate the format. The format needed is: 15/MAR/17 09:31:08.000000000

Currently the below is the closest I can get, I am not sure how to change MM to display MAR instead of 03.

SELECT TO_CHAR
    (SYSDATE, 'DD/MM/YY HH24:MI:SS.SSSSSSSSS')
FROM DUAL;

Thanks a mill in advance

Answer 1

MON will display Month instead of numeric.

SELECT TO_CHAR
    (SYSDATE, 'DD/MON/YY HH24:MI:SS."000000000"')
FROM DUAL;

Answer 2

To convert to text in the format you want (not that you should need to unless the DB design is awful).

substr(to_char(systimestamp,'DD/MON/YY HH24:MI:SS.FF')  || '000000000', 1, 28)

or

to_char(sysdate,'DD/MON/YY HH24:MI:SS')  || '.000000000'

Details on the format mask can be found here.

Note: sysdate does not return the fraction of a second, so if this is required, use systimestamp