Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I ran two simple sql statements

select length(to_char(sysdate, 'fmMonth')) from dual;
select length(to_char(sysdate, 'Month')) from dual;

The length of the first statement was 4 (It's June) and the length of the second statement was 9. Why does it pad 5 extra characters unless you specifically ask it not to?

The only reason I could come up with was that the month with the most amount of characters is september (nls_date_language set 'English') which has 9 character.

Second question is if you changed your nls_date_language variable would you also change the amount of padding?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

The "why" goes back to the fact that when Oracle was originally built, output almost always went to some sort of fixed-width medium (either a terminal window or a physical printer). That meant that everyone's general preference that output widths be fixed since it made sure that everything was in alignment without the programmer or the application having to do any work. Today, of course, things aren't rendered in fixed width fonts all that often, the output of SQL statements is rarely sent to a printer, and applications have controls that make it very easy to present data in grids without anyone having to worry about blank padding output to make it align. But the behavior of functions like TO_CHAR was defined back in the olden days so when the preferred behavior changes, the only way to handle that without breaking backwards compatibility is to provide new format masks that provide the newly desired behavior.

share|improve this answer
    
Interesting bit of history! –  spitfiredd Jun 4 '13 at 10:38

if you changed your nls_date_language variable would you also change the amount of padding?

Yes, you would.

http://sqlfiddle.com/#!4/007bd/49

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.