Search code examples
sqloracleoracle10g

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

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?

Solution

  • 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 ...