Home » SQL & PL/SQL » SQL & PL/SQL » find last wedday
find last wedday [message #227780] Thu, 29 March 2007 07:12 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
any problem in this query

select decode(to_char(sysdate,'DY'),'WED',sysdate,last_day(sysdate,'WED')) from dual;

thanks,
srinivas

Re: find last wedday [message #227786 is a reply to message #227780] Thu, 29 March 2007 07:23 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think you're getting confused between last_day and next_day.
Re: find last wedday [message #227787 is a reply to message #227780] Thu, 29 March 2007 07:25 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
In Oracle/PLSQL, the last_day function returns the last day of the month based on a date value.

The syntax for the last_day function is:

last_day( date )

date is the date value to use to calculate the last day of the month.



For example:

last_day(to_date('2003/03/15', 'yyyy/mm/dd')) would return Mar 31, 2003
last_day(to_date('2003/02/03', 'yyyy/mm/dd')) would return Feb 28, 2003
last_day(to_date('2004/02/03', 'yyyy/mm/dd')) would return Feb 29, 2004
Re: find last wedday [message #227797 is a reply to message #227780] Thu, 29 March 2007 07:34 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
select next_day(last_day(sysdate)+1-7,'WED') from dual
Re: find last wedday [message #228060 is a reply to message #227797] Sat, 31 March 2007 02:03 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select next_day(to_date(sysdate) - 8,'WED') from dual

this will return last wednesday
Re: find last wedday [message #228064 is a reply to message #228060] Sat, 31 March 2007 02:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
muzahidul islam wrote on Sat, 31 March 2007 09:03
select next_day(to_date(sysdate) - 8,'WED') from dual

this will return last wednesday

Sysdate is already a date, so you should not do a to_date on it.
Re: find last wedday [message #228067 is a reply to message #228064] Sat, 31 March 2007 03:07 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
If you want to get only the date of last Wednesday then following query will help, if your requirement is something please let us know.

select next_day(last_day(sysdate) - 7,'WED') from dual;
Re: find last wedday [message #228379 is a reply to message #228067] Mon, 02 April 2007 15:24 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
narayanan.v wrote on Sat, 31 March 2007 03:07
If you want to get only the date of last Wednesday then following query will help, if your requirement is something please let us know.

select next_day(last_day(sysdate) - 7,'WED') from dual;



Incorrect. This will return the last wednesday of the month.

select next_day(sysdate-7,'WED') from dual;

If you are running the query on wednesday, THIS wednesday will be returned. If you still want a week ago, then do

select next_day(sysdate-8,'WED') from dual;
Previous Topic: How to Delete Multiple tables in single query
Next Topic: Intra-Partitioning Query
Goto Forum:
  


Current Time: Thu Dec 08 08:20:24 CST 2016

Total time taken to generate the page: 0.12408 seconds