| New at Creating Functions [message #401147] |
Fri, 01 May 2009 17:35  |
OracleNorwood
Messages: 3
Registered: May 2009
|
Junior Member |
|
|
Hi, I'm new to PL/SQL and currently learning creating functions. I'm running into some problems and looked over my function many times can't seem to find anything else to get my function to work correctly.
I am trying to create a function that accepts a date value and converts it into a string that reads in the format for example: Friday, the Thirteenth of February in the year Two Thousand Nine.
Here's what I have so far. I haven't finished the to_char conversion yet:
Create or replace function fncA_FormattedDate (
in_date in Date)
return VARCHAR2
is
v_date VARCHAR2;
Begin
v_date := to_char(in_date, 'Day');
return v_date;
End;
/
--Test query
select fncA_FormattedDate (sysdate)
from dual;
Can anyone please help me with the function? I think there may be something wrong with my datatypes.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: New at Creating Functions [message #401156 is a reply to message #401154] |
Fri, 01 May 2009 18:42  |
OracleNorwood
Messages: 3
Registered: May 2009
|
Junior Member |
|
|
Here's my create function and test and the results...
Create or replace function fncA_FormattedDate (
in_date in Date)
return VARCHAR2
is
v_date date;
Begin
v_date := to_char(in_date, 'Day');
return v_date;
End;
/ 2 3 4 5 6 7 8 9 10
Function created.
SQL> select fncA_FormattedDate (sysdate)
from dual; 2
select fncA_FormattedDate (sysdate)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "JHUYNH02.FNCA_FORMATTEDDATE", line 7
|
|
|
|
|
|
| Re: New at Creating Functions [message #401158 is a reply to message #401147] |
Fri, 01 May 2009 19:00 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
1 Create or replace function fncA_FormattedDate (
2 in_date in Date)
3 return VARCHAR2
4 is
5 v_date varchar(21);
6 Begin
7 v_date := to_char(in_date, 'Day');
8 return v_date;
9* End;
SQL> /
Function created.
SQL> select fncA_FormattedDate (sysdate) from dual;
FNCA_FORMATTEDDATE(SYSDATE)
--------------------------------------------------------------------------------
Friday
SQL>
>return VARCHAR2
>v_date date;
>return v_date;
Pay attention to datatypes.
Use correct datatypes.
Do not depend upon implicit datatype conversions.
VARCHAR2 is NOT same datatype as DATE
The function is defined to return VARCHAR2 but returns DATE
Many fine coding examples can be found at http://asktom.oracle.com
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
[Updated on: Fri, 01 May 2009 19:16] Report message to a moderator |
|
|
|
|
|
| Re: New at Creating Functions [message #401176 is a reply to message #401147] |
Sat, 02 May 2009 00:04 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>I don't see any use of this function, where as it can be done directly in select clause.
What is your question?
SQL> SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
FRIDAY
SQL> SELECT TO_CHAR(SYSDATE,'WW') from dual;
TO
--
18
With TO_CHAR() DATE datatype can be displayed in any format.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
