Space after 'Month' in TO_CHAR()?
-
<font face="Verdana, Arial, Helvetica, sans-serif"> In Oracle's HTMLDB 10g, I tried to run the following statement:
SELECT 'Today is the ' || TO_CHAR(SYSDATE, 'Ddspth "of" Month, Year')
FROM DUAL;
I recieved as output...
'TODAYISTHE'||TO_CHAR(SYSDATE,'DDSPTH"OF"MONTH,YEAR')
Today is the Third of March , Two Thousand Six
So, weirdly enough, there's a space after the Month.
I tried fiddling with the it...
SELECT 'Today is the ' || TO_CHAR(SYSDATE, 'Ddspth "of" '|| RTRIM('Month', ' ') || ', Year')
FROM DUAL;
and some other wacky solutions. Nothing so far.
Does anyone have any ideas? Or knowledge of why this occurs? I'm about to chalk this up as another one of the quirky WTFs in Oracle's applications, but I just want to discuss it first. </font>
-
The result of the format pattern "Month" is always padded to the length of the longest month name (in the current locale).
What you want is
SELECT 'Today is the ' || rtrim(TO_CHAR(SYSDATE, 'Ddspth "of" Month'))||TO_CHAR(SYSDATE, ', Year')
FROM DUAL;
-
You'll need to prefix the to_char mask with a "fm" like shown below:
SELECT 'Today is the ' || TO_CHAR(SYSDATE, 'fmDdspth "of" Month, Year') from dual;
alec.
-
Change "Month" to "fmMonth". This toggles fill mode, which by default pads all month names out to 9 characters (i.e. the length of the longest month name in the current session language).
-
Oops, just saw Alek beat me to it.
-
Holy fsck... After 12 years of Oracle programming and more than 500 instances of "ltrim(to_char(...", of all things the IHOC tells me how to do it right.
