| Day of the week function in PL/SQL [message #244354] |
Tue, 12 June 2007 07:59  |
haiza
Messages: 22
Registered: June 2007
|
Junior Member |
|
|
|
I'm trying to find if there is an alternative function in pl/sql rather than using SQL to find the day of the week. In SQL you can use to_char(date,'d') but is there a function/package to do this in PL/SQL?
|
|
|
|
|
|
|
|
| Re: Day of the week function in PL/SQL [message #244363 is a reply to message #244361] |
Tue, 12 June 2007 08:09  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
My guess is that the data type of your variable is VARCHAR2 and not a true DATE. What have I won?
MHE
Edit:
SQL> DECLARE
2 v_nodate VARCHAR2 (10) := TO_CHAR (SYSDATE, 'dd-MON-yy');
3 v_dayofweek VARCHAR2 (1);
4 BEGIN
5 v_dayofweek := TO_CHAR(v_nodate, 'D');
6 END;
7 /
v_dayofweek := TO_CHAR(v_nodate, 'D');
*
ERROR at line 5:
ORA-06550: line 5, column 22:
PLS-00307: too many declarations of 'TO_CHAR' match this call
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored[Updated on: Tue, 12 June 2007 08:10] Report message to a moderator |
|
|
|
| Re: Day of the week function in PL/SQL [message #244365 is a reply to message #244363] |
Tue, 12 June 2007 08:18 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
This would also raise an error in sql, so you must be doing something different then you did when you tried it using SQL
SQL> create table faq (my_date varchar2(10));
Table created.
SQL> insert into faq values ('01-01-2007');
1 row created.
SQL> select to_char(my_date, 'D')
2 from faq
3 /
select to_char(my_date, 'D')
*
ERROR at line 1:
ORA-01722: invalid number |
|
|
|
| Re: Day of the week function in PL/SQL [message #244367 is a reply to message #244365] |
Tue, 12 June 2007 08:32 |
haiza
Messages: 22
Registered: June 2007
|
Junior Member |
|
|
Sorry I don't understand. The SQL is working fine but the PL/SQL is not
n_day varchar2(10);
n_day := to_char(date1,'d');
This is where it failed. I have also tried to set the variable n_day to number as well.
|
|
|
|
|
|
|
|
|
|
| Re: Day of the week function in PL/SQL [message #244409 is a reply to message #244375] |
Tue, 12 June 2007 12:50 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| pablolee wrote on Tue, 12 June 2007 15:56 | | Quote: | n_day := to_char(to_date(date1,'dd/mm/yyyy'),'d');
|
well, you could do that, or you could simply use:
Since it's already a date
|
eeh, not quite..
The OP wants a char (day of week).
Your idea is right though: don't do a to_char on retrieval of date1 in the first place, then you won't need the to_date.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
