Home » SQL & PL/SQL » SQL & PL/SQL » Query Date Ranges with select statement (merged 3) (Oracle SQL)
Query Date Ranges with select statement (merged 3) [message #652366] Tue, 07 June 2016 14:56 Go to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
i want to know that is this possible with select statement query to generate date ranges (i.e start_date column and end_date column) for interval of 10 days up-to 50 days back to sysdate. like below

START_DATE END_DATE
29/5/2016 8/6/2016
19/5/2016 29/5/2016
9/5/2016 19/5/2016
29/4/2016 9/5/2016
19/4/2016 29/4/2016

please help anyone

thanking you
Re: Query Date Ranges with select statement [message #652368 is a reply to message #652366] Tue, 07 June 2016 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something like (adjust the limits as you want):
SQL> select sysdate-59+10*level start_date, sysdate-50+10*level from dual connect by level <= 5
  2  /
START_DATE  SYSDATE-50+
----------- -----------
19-APR-2016 28-APR-2016
29-APR-2016 08-MAY-2016
09-MAY-2016 18-MAY-2016
19-MAY-2016 28-MAY-2016
29-MAY-2016 07-JUN-2016

[Updated on: Tue, 07 June 2016 15:05]

Report message to a moderator

Re: Query Date Ranges with select statement [message #652371 is a reply to message #652368] Tue, 07 June 2016 15:11 Go to previous messageGo to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
Thank you so much dear Michel Cadot
Re: Query Date Ranges with select statement [message #652373 is a reply to message #652371] Tue, 07 June 2016 15:53 Go to previous messageGo to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
Dear sir

sorry i could not ask question properly.

required for 55 days like

START_DATE END_DATE
28/5/2016 7/6/2016
17/5/2016 27/5/2016
6/5/2016 16/5/2016
25/4/2016 5/5/2016
14/4/2016 24/4/2016
8/4/2016 13/4/2016
Re: Query Date Ranges with select statement [message #652375 is a reply to message #652366] Tue, 07 June 2016 16:21 Go to previous messageGo to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
Dear Michel Cadot

Please Help. I am beginner.
Generate Date Range [message #652377 is a reply to message #652366] Tue, 07 June 2016 17:01 Go to previous messageGo to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
i want to know that is this possible with select statement query to generate date ranges (i.e start_date column and end_date column) for interval of 10 days up-to 62 days back to sysdate. like below

START_DATE END_DATE
28/5/2016 7/6/2016
17/5/2016 27/5/2016
6/5/2016 16/5/2016
25/4/2016 5/5/2016
14/4/2016 24/4/2016
6/4/2016 13/4/2016

please help anyone

thanking you
Re: Generate Date Range [message #652384 is a reply to message #652377] Wed, 08 June 2016 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See above.
You had the answer.
What is your purpose to repeat your question again and again?

Re: Generate Date Range [message #652385 is a reply to message #652377] Wed, 08 June 2016 00:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The desired results that you posted are actually intervals of 11 days for 66 days, starting with sysdate and going backwards.

SCOTT@orcl_12.1.0.2.0> select  (sysdate+1)-(11*level) start_date,
  2  	     (sysdate+11)-(11*level) end_date
  3  from    dual
  4  connect by level <= 6
  5  /

START_DATE END_DATE
---------- ----------
28/05/2016 07/06/2016
17/05/2016 27/05/2016
06/05/2016 16/05/2016
25/04/2016 05/05/2016
14/04/2016 24/04/2016
03/04/2016 13/04/2016

6 rows selected.
Re: Generate Date Range [message #652403 is a reply to message #652385] Wed, 08 June 2016 04:15 Go to previous messageGo to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
Please consider, my last date range (6/4/2016 - 13/4/2016) is not for 10 days.
Re: Generate Date Range [message #652408 is a reply to message #652403] Wed, 08 June 2016 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please consider to explain in details the actual output you want with sentences that are consistent with the result.

Re: Generate Date Range [message #652425 is a reply to message #652403] Wed, 08 June 2016 11:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select  greatest ((sysdate+1)-(11*level), sysdate-62) start_date,
  2  	     (sysdate+11)-(11*level) end_date
  3  from    dual
  4  connect by level <= 6
  5  /

START_DATE END_DATE
---------- ----------
29/05/2016 08/06/2016
18/05/2016 28/05/2016
07/05/2016 17/05/2016
26/04/2016 06/05/2016
15/04/2016 25/04/2016
07/04/2016 14/04/2016

6 rows selected.
icon14.gif  Re: Generate Date Range [message #652488 is a reply to message #652425] Fri, 10 June 2016 01:48 Go to previous messageGo to next message
basitfaazal
Messages: 7
Registered: June 2016
Location: Karachi Pakistan
Junior Member
thank you so much sir
Re: Generate Date Range [message #652495 is a reply to message #652488] Fri, 10 June 2016 08:36 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
lol. No one ever accused Barbara of being a sir.
Previous Topic: RAISE_APPLICATION_ERROR to display multiple line
Next Topic: Listing individual average price
Goto Forum:
  


Current Time: Fri Mar 29 05:47:48 CDT 2024