Home » SQL & PL/SQL » SQL & PL/SQL » last wednesday in month
last wednesday in month [message #233836] Fri, 27 April 2007 13:25 Go to next message
kazik
Messages: 13
Registered: July 2006
Location: Poland
Junior Member

hey,
I have

declare
my_date date:='2007-04-27';
begin
...
dbms_output.put_line(next_day(last_day(my_date)-7,4));
...
end;

where is misteake?, because oracle says
"ORA-01846: niepoprawny dzień tygodnia" (wrong day of week)

regards
kazik

Re: last wednesday in month [message #233838 is a reply to message #233836] Fri, 27 April 2007 13:35 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
kazik wrote on Fri, 27 April 2007 14:25
hey,
I have

declare
my_date date:='2007-04-27';
begin




Valid syntax would be
my_date date:=TO_DATE('2007-04-27','YYYY-MM-DD');
Re: last wednesday in month [message #233841 is a reply to message #233838] Fri, 27 April 2007 13:43 Go to previous messageGo to next message
kazik
Messages: 13
Registered: July 2006
Location: Poland
Junior Member

the same misteake
Re: last wednesday in month [message #233842 is a reply to message #233836] Fri, 27 April 2007 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Second parameter of next_day is a string containing a day name.

Regards
Michel
Re: last wednesday in month [message #233844 is a reply to message #233841] Fri, 27 April 2007 13:48 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
SCOTT FOO>l
  1  declare
  2  my_date date:=TO_DATE('2007-04-27','YYYY-MM-DD');
  3  fff date;
  4  begin
  5   select next_day(last_day(my_date)-7,4) into fff from dual;
  6   dbms_output.put_line(fff);
  7* end;
SCOTT FOO>/
25-APR-07

PL/SQL procedure successfully completed.

[Updated on: Fri, 27 April 2007 13:49]

Report message to a moderator

Re: last wednesday in month [message #233845 is a reply to message #233844] Fri, 27 April 2007 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    my_date date:=TO_DATE('2007-04-27','YYYY-MM-DD');
  3    fff date;
  4  begin
  5    select next_day(last_day(my_date)-7,4) into fff from dual;
  6    dbms_output.put_line(fff);
  7  end;
  8  /
26/04/07

PL/SQL procedure successfully completed.

Regards
Michel
Re: last wednesday in month [message #233849 is a reply to message #233844] Fri, 27 April 2007 14:20 Go to previous messageGo to next message
kazik
Messages: 13
Registered: July 2006
Location: Poland
Junior Member

it works, thanks
regards
kazik
Re: last wednesday in month [message #233851 is a reply to message #233849] Fri, 27 April 2007 14:28 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
What did you do differently, because what I posted is just the same thing I said in my first reply?
Re: last wednesday in month [message #233852 is a reply to message #233851] Fri, 27 April 2007 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not in the same territory (France), so day numbers are not the same.
This is why you have to use day name even if day number seems to work when documentation says not:
Quote:
NEXT_DAY returns the date of the first weekday named by char that is later than the date date. [...] The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation.

Regards
Michel
Re: last wednesday in month [message #233885 is a reply to message #233852] Fri, 27 April 2007 20:48 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Heh, Michel, I was actually referring to OP. I figured your answer had to do something with your NLS setting, but I was asking OP why our fully detailed answers were any different than my first response which wasn't a full spoon feed, yet had the same TO_DATE function in it.
Re: last wednesday in month [message #233891 is a reply to message #233885] Sat, 28 April 2007 00:13 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh, sorry, I misread your answer. Embarassed

Regards
Michel
Previous Topic: date
Next Topic: View issue / inheritance advice (merged)
Goto Forum:
  


Current Time: Tue Dec 06 16:27:38 CST 2016

Total time taken to generate the page: 0.05546 seconds