Home » SQL & PL/SQL » SQL & PL/SQL » Day of the week function in PL/SQL
Day of the week function in PL/SQL [message #244354] Tue, 12 June 2007 07:59 Go to next message
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 #244355 is a reply to message #244354] Tue, 12 June 2007 08:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
what happens if you use
variable := to_char(<date>, 'D');

in pl/sql?

[Updated on: Tue, 12 June 2007 08:00]

Report message to a moderator

Re: Day of the week function in PL/SQL [message #244361 is a reply to message #244355] Tue, 12 June 2007 08:04 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
An error message will be displayed:= PLS-00307 too many declarations of 'to_char' match this call
Re: Day of the week function in PL/SQL [message #244363 is a reply to message #244361] Tue, 12 June 2007 08:09 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #244369 is a reply to message #244367] Tue, 12 June 2007 08:35 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
OK, i found what the problem is now. The date field I got back is to_char. I just convert this back using to_date and it's working ok.
n_day := to_char(to_date(date1,'dd/mm/yyyy'),'d');
Re: Day of the week function in PL/SQL [message #244371 is a reply to message #244367] Tue, 12 June 2007 08:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Edit: I won Very Happy

MHE

[Updated on: Tue, 12 June 2007 08:36]

Report message to a moderator

Re: Day of the week function in PL/SQL [message #244375 is a reply to message #244369] Tue, 12 June 2007 08:56 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
n_day := to_char(to_date(date1,'dd/mm/yyyy'),'d');

well, you could do that, or you could simply use:
n_day := date1;


Since it's already a date
Re: Day of the week function in PL/SQL [message #244409 is a reply to message #244375] Tue, 12 June 2007 12:50 Go to previous messageGo to next message
Frank
Messages: 7880
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:
n_day := date1;


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.
Re: Day of the week function in PL/SQL [message #244990 is a reply to message #244409] Thu, 14 June 2007 16:27 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Yup, helps if I read the post a bit more thoroughly. Thanks Smile
Previous Topic: problem with oracle sql+
Next Topic: how to use db link in procedure
Goto Forum:
  


Current Time: Sat Dec 10 20:22:38 CST 2016

Total time taken to generate the page: 0.23060 seconds