SQL> -- original . . .
SQL> select
2 to_char( sysdate, 'Day "the" Ddth "of" Month, yyyy' ) dt
3 from dual;
DT
----------------------------------------
Friday the 13th of May , 2016
SQL>
SQL> -- collapse repeated spaces . . .
SQL> select
2 regexp_replace(
3 to_char( sysdate, 'Day "the" Ddth "of" Month, yyyy' ),
4 ' * *', ' ') datesp
5 from dual;
DATESP
----------------------------------------
Friday the 13th of May , 2016
SQL>
SQL> -- and space before commma . . .
SQL> select
2 regexp_replace(
3 to_char( sysdate, 'Day "the" Ddth "of" Month, yyyy' ),
4 ' *(,*) *', '\1 ') datesp
5 from dual;
DATESP
----------------------------------------
Friday the 13th of May, 2016
SQL>
SQL> -- space before punctuation . . .
SQL> select
2 regexp_replace(
3 to_char( sysdate, 'Day "the" Ddth "of" Month, yyyy' ),
4 ' *([.,/:;]*) *', '\1 ') datesp
5 from dual;
DATESP
----------------------------------------
Friday the 13th of May, 2016
Why are there extra spaces between my month and day? Why does't it just put them next to each other?
So your output will be aligned.
If you don't want padding use the format modifier FM
:
SELECT TO_CHAR (date_field, 'fmMonth DD, YYYY')
FROM ...;
Reference: Format Model Modifiers
You should use fm element to delete blank spaces.
SELECT TO_CHAR(sysdate, 'fmDAY DD "de" MONTH "de" YYYY') CURRENT_DATE
FROM dual;
select to_char(sysdate, 'DD-fmMONTH-YYYY') "Date" from Dual;
The above query result will be as given below.
01-APRIL-2019
try this:-
select to_char(to_date('01/10/2017','dd/mm/yyyy'),'fmMonth fmDD,YYYY') from dual;
select to_char(sysdate,'fmMonth fmDD,YYYY') from dual;
Source: Stackoverflow.com