Home » SQL & PL/SQL » SQL & PL/SQL » how can get five days
how can get five days [message #666424] Fri, 03 November 2017 11:16 Go to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
i need query to show only 5 dates
exanple
i have date of the table like this

1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
20/2/2017
6/1/2015
7/1/2017
i want only show this dates
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017

also show only 5 dates with right sequence
Re: how can get five days [message #666425 is a reply to message #666424] Fri, 03 November 2017 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Please find desired solution below.

1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017

Re: how can get five days [message #666427 is a reply to message #666424] Fri, 03 November 2017 11:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Is this a school homework assignment?

You need to provide the CREATE TABLE statement and the INSERT statements, and you must show what SQL you have tried so far.

Please remember to do this with copy/paste from SQL*Plus, and use [code] tags to make it readable:
How to use [code] tags and make your code easier to read
Re: how can get five days [message #666430 is a reply to message #666424] Fri, 03 November 2017 11:30 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member

CREATE TABLE SCOTT.TEST1
(
  P_1  NUMBER,
  P_2  DATE
)




CREATE TABLE SCOTT.TEST1
(
  Insert into TEST1
   (P_1, P_2)
 Values
   (1, TO_DATE('01/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (2, TO_DATE('01/27/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (1, TO_DATE('01/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (3, TO_DATE('01/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (1, TO_DATE('01/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (1, TO_DATE('01/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (1, TO_DATE('01/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (3, TO_DATE('01/25/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
   (P_1, P_2)
 Values
   (2, TO_DATE('01/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
)


i want only show 5 dates for every month
Re: how can get five days [message #666431 is a reply to message #666430] Fri, 03 November 2017 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i want only show 5 dates for every month

which 5 dates?
Re: how can get five days [message #666432 is a reply to message #666424] Fri, 03 November 2017 11:46 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
only want show every five dates at every month
example
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
6/1/2017

here i want only show date start from 1/1/2017 end 5/1/2017

another example
1/4/2017
2/4/2017
3/4/2017
4/4/2017
5/4/2017
6/4/2017
here i want only show date start from 1/4/2017 end 5/4/2017
Re: how can get five days [message #666433 is a reply to message #666432] Fri, 03 November 2017 11:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
https://docs.oracle.com/database/122/SQLRF/BETWEEN-Condition.htm#SQLRF52164
Re: how can get five days [message #666434 is a reply to message #666424] Fri, 03 November 2017 11:58 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
but data in this table will be dynamic
and cant not need to use between
i need how can get this data dynamic
Re: how can get five days [message #666435 is a reply to message #666434] Fri, 03 November 2017 12:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Sorry, man, I do not understand what you are saying. Try saying it in SQL, which should be our common language Smile

I'm off out now.
Re: how can get five days [message #666436 is a reply to message #666432] Fri, 03 November 2017 12:38 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Select * from test1
where to_char(p2,'DD') between '01' and '05';
Re: how can get five days [message #666451 is a reply to message #666436] Mon, 06 November 2017 00:57 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
If you want to find consecutive dates (as I understand your question), have a look at the Tabibitosan Method. There you can find good examples for date ranges too.

[Updated on: Mon, 06 November 2017 00:59]

Report message to a moderator

Re: how can get five days [message #666454 is a reply to message #666451] Mon, 06 November 2017 02:02 Go to previous message
quirks
Messages: 82
Registered: October 2014
Member
If you only look at P_1 then it could be something like:
SELECT   *
    FROM (SELECT DISTINCT P_2
            FROM TEST1)
   WHERE ROWNUM < 6
ORDER BY P_2;

If you need the first 5 dates for each P_1 then this should work:
SELECT   P_1, P_2
    FROM (SELECT P_1, P_2, ROW_NUMBER() OVER(PARTITION BY P_1 ORDER BY P_2) AS RN FROM TEST1)
   WHERE RN < 6
ORDER BY P_1, P_2;
Previous Topic: Prevent deletion
Next Topic: Order of Trigger Types
Goto Forum:
  


Current Time: Thu Mar 28 15:01:59 CDT 2024