guys,

I have to display the day of tomorrow in the format: January 10th of year 2019.
I created this query:

SELECT TO_CHAR((SYSDATE + 1),'Month DD (ddspth) "of year" YYYY') AS tomorrow FROM DUAL;

The output is:

May       23 (twenty-third) of year 2023

How can I get the output in the desired format?

Thanks in advance.

>Solution :

Don’t spell the word out; and use FM to suppress blank padding:

SELECT TO_CHAR((SYSDATE + 1),'FMMonth ddth "of year" YYYY') AS tomorrow FROM DUAL;

May 24th of year 2022

db<>fiddle demo.

Month names are NLS-sensitive, so someone running this in a session with a different date language set will see something else – which you can override. The th will be in English anyway, as will your character literal part. You might want to force the month names to English to match. That might be beyond the requirements for this exercise but I’ve included how to to it in the db<>fiddle.