contactmatt contactmatt - 2 years ago 114
SQL Question

Oracle Date TO_CHAR('Month DD, YYYY') has extra spaces in it

When I do...

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

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?

Ben Ben
Answer Source

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,

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


select to_char(date_field,'mon dd, yyyy')
  from ...  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download