Home » SQL & PL/SQL » SQL & PL/SQL » next_day function help
next_day function help [message #239762] Wed, 23 May 2007 04:38 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

our weekly periods go from Saturday to Friday.

I was hoping that there might be a next_day function, but going backwards - like a previous day.

basically I want to return the date of the saturday from the week before the current one.
so running it anytime from the 19th to the 25th May would return the 12th.

I suppose I could just use next_day(sysdate-8, 'Saturday')

but is there a different method?
thanks in advance,
Matt
Re: next_day function help [message #239763 is a reply to message #239762] Wed, 23 May 2007 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No. This is the one that comes into my mind.

Regards
Michel
Re: next_day function help [message #239765 is a reply to message #239763] Wed, 23 May 2007 04:47 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

the only problem with that is that it doesn;t work.

If I run the query on a wednesday for example.
Wednesday -8 is tuesday.
and the next saturday is the one after that.
It would work if I ran it on the saturday and wanted the previous saturday's date..

there must be an easy solution that I just cannot see...
Re: next_day function help [message #239769 is a reply to message #239762] Wed, 23 May 2007 04:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon you've already got the best solution.
You could try
next_day(sysdate-1,'Saturday')-7
but there's not going to be much in it.
Just create a functionwrapper for this called 'PREVIOUS_DAY' and use it like any other function:
CREATE OR REPLACE FUNCTION previous_day (p_date in date, p_day in  varchar2) return date is
BEGIN
  return next_day(p_date-1, p_day)-7;
END;
/
Previous Topic: How to checking duplicated rows and copy it !!!
Next Topic: Connect by Prior
Goto Forum:
  


Current Time: Wed Dec 07 10:53:54 CST 2016

Total time taken to generate the page: 0.24506 seconds