contactmatt contactmatt - 6 months ago 27
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')
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?

Ben Ben
Answer

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