OraFAQ Forum: SQL & PL/SQL » Range by Day and Month

Home » SQL & PL/SQL » SQL & PL/SQL » Range by Day and Month

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Range by Day and Month [message #475978]

Mon, 20 September 2010 06:51

meim
Messages: 35
Registered: July 2009

Member

Hello,

I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.

For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.

e.g. a range could be from today:Sept,20 to Apr,18.

so what i was trying is to select doing the following.

select TABLE_X_ID, TABLE_X_BIRTH_DATE
from TABLE_X
where to_date(TABLE_X_BIRTH_DATE, 'DD/MM')
between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM')
and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')

first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.

Any help would be appreciated.

Meim

Report message to a moderator

Re: Range by Day and Month [message #475982 is a reply to message #475978]

Mon, 20 September 2010 07:19

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/
if TABLE_X_BIRTH_DATE is of DATE datatype then TO_DATE of this column is silly. It is like saying I want to convert dollars into dollars.

2/
Quote:

sysdate+7months ahead

ADD_MONTHS

3/
Quote:

only the day and month

Not clear.

4/
Quote:

first am not sure if BETWEEN & AND will work for this case

It will/might... when correctly used.

Regards
Michel

Report message to a moderator

Re: Range by Day and Month [message #475997 is a reply to message #475982]

Mon, 20 September 2010 09:41

nikko
Messages: 9
Registered: September 2010

Junior Member

hello meim.

if TABLE_X_BIRTH_DATE is of DATE datatype then should u use the EXTRACT function.

Regards.

Report message to a moderator

Re: Range by Day and Month [message #476005 is a reply to message #475978]

Mon, 20 September 2010 10:13

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

here is a partial solution. You will have to add and subtract 'bits' as appropriate, but the general idea is in there

WITH t AS (SELECT add_months(DATE'2010-10-10',-LEVEL) bdate, level idx 
           FROM dual
           CONNECT BY LEVEL <=100)
SELECT bdate, idx, to_char(bdate,'mmdd-yyyy') x 
FROM t
WHERE to_char(bdate,'mmdd') BETWEEN '0801' AND '1031'
order by 1

Report message to a moderator

Re: Range by Day and Month [message #476015 is a reply to message #475978]

Mon, 20 September 2010 12:56

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

meim wrote on Mon, 20 September 2010 07:51

For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.

Ok, is this another case where I do not understand the question, because why wouldn't

where date_column between sysdate and sysdate+add_months(sysdate,7)

not work?

Report message to a moderator

Re: Range by Day and Month [message #476038 is a reply to message #475978]

Mon, 20 September 2010 17:36

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> CREATE TABLE table_x AS
  2  SELECT empno table_x_id,
  3  	    hiredate table_x_birth_date
  4  FROM   emp
  5  /

Table created.

SCOTT@orcl_11gR2> SELECT * FROM table_x
  2  /

TABLE_X_ID TABLE_X_B
---------- ---------
      7369 17-DEC-80
      7499 20-FEB-81
      7521 22-FEB-81
      7566 02-APR-81
      7654 28-SEP-81
      7698 01-MAY-81
      7782 09-JUN-81
      7788 19-APR-87
      7839 17-NOV-81
      7844 08-SEP-81
      7876 23-MAY-87
      7900 03-DEC-81
      7902 03-DEC-81
      7934 23-JAN-82

14 rows selected.

SCOTT@orcl_11gR2> SELECT table_x_id,
  2  	    table_x_birth_date
  3  FROM   table_x
  4  WHERE  TO_CHAR (table_x_birth_date, 'DD/MM') IN
  5  	    (SELECT TO_CHAR (SYSDATE+ROWNUM-1, 'DD/MM')
  6  	     FROM   DUAL
  7  	     CONNECT BY LEVEL <= 211)
  8  /

TABLE_X_ID TABLE_X_B
---------- ---------
      7369 17-DEC-80
      7499 20-FEB-81
      7521 22-FEB-81
      7566 02-APR-81
      7654 28-SEP-81
      7839 17-NOV-81
      7900 03-DEC-81
      7902 03-DEC-81
      7934 23-JAN-82

9 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Range by Day and Month [message #476040 is a reply to message #476038]

Mon, 20 September 2010 20:05

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

[quote title=Barbara Boehmer wrote on Mon, 20 September 2010 18:36[/quote]

As in most cases, February 29 messes things up. Your code will select people born on February 29 on leap year only:

SQL> create table emp1 as select * from emp
  2  /

Table created.

SQL> update emp1
  2  set hiredate = date '1984-02-29'
  3  where ename = 'KING'
  4  /

1 row updated.

SQL> SELECT  ename,
  2          hiredate
  3    FROM  emp1
  4    WHERE  TO_CHAR(hiredate,'DD/MM') IN (
  5                                         SELECT  TO_CHAR(SYSDATE + ROWNUM - 1,'DD/MM')
  6                                           FROM  DUAL
  7                                           CONNECT BY LEVEL <= 211
  8                                        )
  9  /

ENAME      HIREDATE
---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
JONES      02-APR-81
MARTIN     28-SEP-81
JAMES      03-DEC-81
FORD       03-DEC-81
MILLER     23-JAN-82

8 rows selected.

SQL>

SY.

Report message to a moderator

Re: Range by Day and Month [message #476043 is a reply to message #475982]

Tue, 21 September 2010 00:42

meim
Messages: 35
Registered: July 2009

Member

Hi

Michel Cadot wrote on Mon, 20 September 2010 07:19

1/
if TABLE_X_BIRTH_DATE is of DATE datatype then TO_DATE of this column is silly. It is like saying I want to convert dollars into dollars.

OK

2/
Quote:
sysdate+7months ahead

ADD_MONTHS

Thanks, I wasn't familiar wd such function

3/
Quote:
only the day and month

Not clear.

Extract the day and month without the year from the date

4/
Quote:
first am not sure if BETWEEN & AND will work for this case

It will/might... when correctly used.

Yes

Regards
Michel

Thanks Michel for your reply.

Report message to a moderator

Re: Range by Day and Month [message #476044 is a reply to message #475997]

Tue, 21 September 2010 00:45

meim
Messages: 35
Registered: July 2009

Member

nikko wrote on Mon, 20 September 2010 09:41

hello meim.

if TABLE_X_BIRTH_DATE is of DATE datatype then should u use the EXTRACT function.

Regards.

Hi,

Yes extract function can help, but i couldn't just pass for example the sysdate to get the year, i dunno if the date should be hard-coded within the function with format 'yyyy-mm-dd' to be function.

anyway, thanks for your reply Smile

Report message to a moderator

Re: Range by Day and Month [message #476045 is a reply to message #476005]

Tue, 21 September 2010 00:48

meim
Messages: 35
Registered: July 2009

Member

pablolee wrote on Mon, 20 September 2010 10:13

here is a partial solution. You will have to add and subtract 'bits' as appropriate, but the general idea is in there
WITH t AS (SELECT add_months(DATE'2010-10-10',-LEVEL) bdate, level idx 
           FROM dual
           CONNECT BY LEVEL <=100)
SELECT bdate, idx, to_char(bdate,'mmdd-yyyy') x 
FROM t
WHERE to_char(bdate,'mmdd') BETWEEN '0801' AND '1031'
order by 1

Hi,

the problem is the interval of the Day and Month is not fixed in my case in which it should be [SYSDATE, ADD_MONTHS(sysdate, 7)] which mean it is not always that the beginning of the interval is lower that the end of the interval which makes BETWEEN & AND not working all the time. wht do you think ?

Thanks for your reply.

Report message to a moderator

Re: Range by Day and Month [message #476046 is a reply to message #476045]

Tue, 21 September 2010 01:03

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

meim wrote on Tue, 21 September 2010 06:48

Hi,

the problem is the interval of the Day and Month is not fixed in my case in which it should be [SYSDATE, ADD_MONTHS(sysdate, 7)] which mean it is not always that the beginning of the interval is lower that the end of the interval which makes BETWEEN & AND not working all the time. wht do you think ?

Thanks for your reply.

Well, as I said, it is a partial solution. You need to apply some effort.
Quote:

which mean it is not always that the beginning of the interval is lower that the end of the interval

You have so far, given no information that implies this. So far you have said that the range is to be the 7 months proceeding the current date (year being taken out of the equation. Is this not the case?
Have you made any attempt to adapt my example to your needs?

CM: fixed a quote tag

[Updated on: Tue, 21 September 2010 06:08] by Moderator

Report message to a moderator

Re: Range by Day and Month [message #476047 is a reply to message #476015]

Tue, 21 September 2010 01:32

meim
Messages: 35
Registered: July 2009

Member

joy_division wrote on Mon, 20 September 2010 12:56

meim wrote on Mon, 20 September 2010 07:51

For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.

Ok, is this another case where I do not understand the question, because why wouldn't
where date_column between sysdate and sysdate+add_months(sysdate,7)
not work?

Hi,

I can't say this directly as the date_colum is Birth Date which mean year wont match the year of the sysdate. plus, the problem i mentioned in my previous reply about using BETWEEN & AND.

Thanks for your reply

Report message to a moderator

Re: Range by Day and Month [message #476050 is a reply to message #476047]

Tue, 21 September 2010 02:00

meim
Messages: 35
Registered: July 2009

Member

Hi Barbara Boehmer & syakobson,

I tried your solution and it do the work.

Thanks both of you.

Report message to a moderator

Re: Range by Day and Month [message #476096 is a reply to message #476050]

Tue, 21 September 2010 06:06

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

meim wrote on Tue, 21 September 2010 03:00

Hi I tried your solution and it do the work.

First of all, I did not offer any solutions. I just pointed out Barbara's solution does not deal with Feb 29 properly. Below is my suggestion. It assumes humans do not live more than 150 years (with some cushion for future medical break-throughs Cool

). Table emp1 is same as emp with King's hiredate changed to Feb 29. Solution simply generates "anniversary dates" and selects people who's "anniversary dates" are within 7 months from today:

SELECT  ename,
        hiredate
  from  emp1,
        (
         select  (level - 1) * 12 offset
           from  dual
           connect by level <= 151
        )
  where add_months(hiredate,offset) >= trunc(sysdate)
    and add_months(hiredate,offset) <  add_months(trunc(sysdate),7)
/

ENAME      HIREDATE
---------- ---------
SCOTT      19-APR-87
KING       29-FEB-84
MARTIN     28-SEP-81
JAMES      03-DEC-81
FORD       03-DEC-81
MILLER     23-JAN-82
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
JONES      02-APR-81

10 rows selected.

SQL>

SY.
P.S. You might want to add DISTINCT to the above query in case search interval spans one year

[Updated on: Tue, 21 September 2010 06:09]

Report message to a moderator

Previous Topic:	How to get records for max value only
Next Topic:	Overloading in oracle package

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 17 19:01:13 CDT 2025