Oracle Date TO_CHAR('Month DD, YYYY') has

问题:

When I do...

Select TO_CHAR (date_field, 'Month DD, YYYY')
from...

I get the following:

July      01, 2011
April     01, 2011
January   01, 2011

Why are there extra spaces between my month and day? Why doesn't it just put them next to each other?

回答1:

if you use 'Month' in to_char it right pads to 9 characters; you have to use the abbreviated 'MON', or to_char then trim and concatenate it to avoid this. See, http://www.techonthenet.com/oracle/functions/to_char.php

select trim(to_char(date_field, 'month')) || ' ' || to_char(date_field,'dd, yyyy')
  from ...

or

select to_char(date_field,'mon dd, yyyy')
  from ...  

回答2:

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

回答3:

You should use fm element to delete blank spaces.

SELECT TO_CHAR(sysdate, 'fmDAY DD "de" MONTH "de" YYYY') CURRENT_DATE
FROM   dual;

回答4:

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

回答5:

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;