-
I'm a new student, and dont know if its ok to be posting here or not, so tell me right off what/where is appropriate for me.
That being said, I'm trying to get rid of the trailing spaces that I get when do a TO_CHAR in a PL/SQL program. Here it is for all to check out and if you can get back to me with the correct way to go about it: DECLARE
state varchar2 (20);
statehooddate DATE;
BEGIN
state := 'Wyoming';
statehooddate := TO_DATE('07/10/1890', 'MM/DD/YYYY');
statehooddate := RTRIM(statehooddate);
DBMS_OUTPUT.PUT_LINE(state || ' became a state on ' ||
TO_CHAR(statehooddate, 'Month DD, YYYY')||'.');
END;
Wyoming became a state on July 10, 1890. As you can see the padded area is after "July", and I want to know how to remove them. I've toyed with Instr and Substr with no luck either, but I'm sure somebody here can help me with this.
THANKS!!!!!!
-
Hi
try this one
Format Modifier : 'fm'
if FM is omitted, the month is blank-padded to nine characters
You have to specify fm if u don't want any blank spaces in the
to_char() funtion
BEGIN
state := 'Wyoming';
statehooddate := TO_DATE('07/10/1890', 'MM/DD/YYYY');
statehooddate := RTRIM(statehooddate);
DBMS_OUTPUT.PUT_LINE(state || ' became a state on ' ||
TO_CHAR(statehooddate, 'fmMonth DD, YYYY')||'.');
END;
Good luck
Sarath
-
no diff
Hi sarath,
i do not see any difference.....see both the queries...
SQL> select to_char(sysdate,'fmMonth DD, YYYY') from dual;
TO_CHAR(SYSDATE,'F
------------------
September 24, 2001
SQL> select to_char(sysdate,'Month DD, YYYY') from dual;
TO_CHAR(SYSDATE,'M
------------------
September 24, 2001
Insead i used(remove the space in the format)...see the follo queries
SQL> select to_char(sysdate,'fmMonthDD, YYYY') from dual;
TO_CHAR(SYSDATE,'
-----------------
September24, 2001
SQL> select to_char(sysdate,'MonthDD, YYYY') from dual;
TO_CHAR(SYSDATE,'
-----------------
September24, 2001
I hope this is what u require....if i am wrong pls correct me..
Thanks
pras
-
Hi pras,
U r wrong u r thinking in only one direction that is for a predefined date , but here the problem is something different he is explicitly converting to date and then using to_Char()
Your statement will work only when we take a date field or sysdate but here the scenario is different
test this one
SARATH=> select to_char(to_date '22/aug/01','dd/mon/yy'),'Month DD, YYYY') from
dual;
TO_CHAR(TO_DATE('2
------------------
August 22, 2001
when u form a date by using to_date function and then using the to_char() function the spaces will come.to avoid that one
we have to use 'fm' mode in format modes of dates while using to_char function.
as u said removing the spaces in the format will not solve the problem test this statement
SARATH=>*select to_char(to_date('22/aug/01','dd/mon/yy'),'MonthDD, YYYY') from dual;
TO_CHAR(TO_DATE('
-----------------
August 22, 2001
and finally test this one
SARATH=>*select to_char(to_date('22/aug/01','dd/mon/yy'),'fmMonth DD, YYYY') from dual;
TO_CHAR(TO_DATE('2
------------------
August 22, 2001
so finally ur statement will not work for all scenarios
Thanks
Sarath
-
thanks
Hi Sarath
Yeah ur right....
Thanks a lot for clearing my doubt....
Have a nice time
pras
-
Hi Pras,
No Probs Thanks and have a nice time
Sarath
-
Works great!
Sarath!
Thank you so much for your help. The 'fm' does the trick perfectly for me!
Have a good one!
Netrix.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|