oracle sql query date

Dear, I am looking to query my database to find people birthdate within a certain range of date.
This range is defined by today + 7 days (In mind, who has his birthday this week)
At the moment, the only thing I found is to split the Month and the Day of the SYSDATE to compare with my birthdate field, it works correctly for month but I don't found a way to specify the range for the days (syntax error)...it should be something like in my code

Thanks for your help
select peopleid where (TO_CHAR(PEOPLE.BIRTHDATE, 'MM') = TO_CHAR(SYSDATE, 'MM')) AND (TO_CHAR(PEOPLE.BIRTHDATE, 'DD') between TO_CHAR(SYSDATE, 'DD') and TO_CHAR(SYSDATE, 'DD') +7

Open in new window

pascal_moiseAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
same queries as above but using  your table and column names


SELECT birthdate
FROM (SELECT birthdate,
             TRUNC(SYSDATE) today,
             CASE
                 -- person was born a leap-day, but this is not a leap year
                 -- then treat the person as having been born on 3/1
                 WHEN TO_CHAR(birthdate, 'mmdd') = '0229'
      AND             TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
                 THEN
                     TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
                 ELSE
                     TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(birthdate, 'mmdd'), 'yyyymmdd')
             END
                 birthdaythisyear
      FROM people)
WHERE birthdaythisyear BETWEEN today AND today + 7

---- another way, the add_months function will treat leap-days as 2/28 in non-leap-years


SELECT   birthdate
FROM     (SELECT TRUNC(SYSDATE) today, people.*, TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(birthdate, 'yyyy') years
          FROM   people)
WHERE    ADD_MONTHS(birthdate, 12 * years) BETWEEN today AND today + 7
      OR ADD_MONTHS(birthdate, 12 * years + 12) BETWEEN today AND today + 7
ORDER BY birthdate
0
 
schwertnerCommented:
Thyere is a format element

DDD Day of year (1-366).

So try to use the condition

SELECT peopleid  FROM the_table
WHERE    TO_NUMBER (birtday, 'DDD)   BETWEEN   ANDTO_NUMBER (sysdate, 'DDD') AND TO_NUMBER (sysdate+7, 'DDD') ;
0
 
AnilData ManagerCommented:
select peopleid  from PEOPLE where mod(PEOPLE.BIRTHDATE-SYSDATE,365)<8


I'm no expert but that should do the trick.


A>
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
schwertnerCommented:
SELECT peopleid  FROM the_table
WHERE    TO_NUMBER (birtday, 'DDD)   BETWEEN   TO_NUMBER (sysdate, 'DDD') AND TO_NUMBER (sysdate+7, 'DDD') ;
0
 
AnilData ManagerCommented:
Just realised that this gives an approximate answer as not all years are 365 days and hence you may need to tweak the parameter 8.
0
 
Pratima PharandeCommented:
try this

SELECT peopleid
 FROM tablename
where TO_DATE(to_char(sysdate,'YYYY') || TO_CHAR(PEOPLE.BIRTHDATE,'MMDD'),'YYYYMMDD')

between to_date(to_char(sysdate+ (1-to_char(sysdate,'D'))))and to_date(to_char(sysdate+ (7-to_char(sysdate,'D'))))
0
 
pascal_moiseAuthor Commented:
Dear,
None of the solution is working,
Schwertner
 TO_NUMBER (people.birthdate, 'DDD') is automatically corrected by visual studio to :
TO_NUMBER(TO_CHAR(PEOPLE.BIRTHDATE); 'MM') and gives invalid number format model

akajohn
(MOD(PEOPLE.BIRTHDATE - SYSDATE, 365) < 8) return an "unable to read data" in the column

Sorry and thanks for your help
0
 
Pratima PharandeCommented:
have you tried my solution ?
0
 
pascal_moiseAuthor Commented:
pratima
yes just now and receive error "your entry cannot be converted to a valid date time value"
0
 
Pratima PharandeCommented:
try this
select to_date(to_char(sysdate+ (1-to_char(sysdate,'D')))) startdayofweek
, to_date(to_char(sysdate+ (7-to_char(sysdate,'D')))) endofweek,
TO_DATE(to_char(sysdate,'YYYY') || TO_CHAR(sysdate,'MMDD'),'YYYYMMDD')

from dual


is it working
0
 
sdstuberCommented:
how do you want to handle people born on leap-day  2/29?

here are 2 methods depending on if you want to consider those dates as 2/28 or 3/1 in non-leap years


SELECT dob
FROM (SELECT dob,
             TRUNC(SYSDATE) today,
             CASE
                       -- person was born a leap-day, but this is not a leap year
                         -- then treat the person as having been born on 3/1
                 WHEN TO_CHAR(dob, 'mmdd') = '0229'
      AND             TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
                 THEN
                     TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
                 ELSE
                     TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(dob, 'mmdd'), 'yyyymmdd')
             END
                 birthdaythisyear
      FROM staff)
WHERE birthdaythisyear BETWEEN today AND today + 7

---- another way, the add_months function will treat leap-days as 2/28 in non-leap-years


SELECT   dob
FROM     (SELECT TRUNC(SYSDATE) today, staff.*, TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(dob, 'yyyy') years
          FROM   staff)
WHERE    ADD_MONTHS(dob, 12 * years) BETWEEN today AND today + 7
      OR ADD_MONTHS(dob, 12 * years + 12) BETWEEN today AND today + 7
ORDER BY dob
0
 
Pratima PharandeCommented:
try this

SELECT peopleid
 FROM tablename
where TO_DATE(to_char(sysdate,'YYYY') || TO_CHAR(PEOPLE.BIRTHDATE, 'MM') || TO_CHAR(PEOPLE.BIRTHDATE, 'DD'),'YYYYMMDD')

between to_date(to_char(sysdate+ (1-to_char(sysdate,'D')))) and to_date(to_char(sysdate+ (7-to_char(sysdate,'D'))))
0
 
pascal_moiseAuthor Commented:
pratima
I just test this and it already gives error
 to_date(to_char(sysdate+ (1-to_char(sysdate,'D'))))
DATA TYPE ERROR IN EXPRESSION
0
 
Pratima PharandeCommented:
try this

SELECT peopleid
 FROM tablename
where TO_DATE(to_char(sysdate,'YYYY') || TO_CHAR(PEOPLE.BIRTHDATE, 'MM') || TO_CHAR(PEOPLE.BIRTHDATE, 'DD'),'YYYYMMDD')
between sysdate+ (1-to_char(sysdate,'D'))and sysdate+ (7-to_char(sysdate,'D'))
0
 
pascal_moiseAuthor Commented:
pratima
this gives INVALID OR MISSING EXPRESSION
 TO_DATE(to_char(sysdate,'YYYY') || TO_CHAR(PEOPLE.BIRTHDATE, 'MM') || TO_CHAR(PEOPLE.BIRTHDATE, 'DD'),'YYYYMMDD')

Looks like I forgot to tell I am using oracle 8i
0
 
sdstuberCommented:
every suggestion above, whether correct or not, should be able to execute in 8i (barring other minor syntax errors like missing quotes)
0
 
sdstuberCommented:
this will fail for Feb 29 birthdays on non-leap years


 TO_DATE(to_char(sysdate,'YYYY') || TO_CHAR(PEOPLE.BIRTHDATE, 'MM') || TO_CHAR(PEOPLE.BIRTHDATE, 'DD'),'YYYYMMDD')

because you can't use to_date to construct an illegal 2/29 date


attempts to count days using TO_NUMBER (birtday, 'DDD')   will also fail for leap years for any birthday 3/1 or later because they will be off by a day if the person was not born in a leap year

and the 365 day method, as already noted also fails to consider leap years




0
 
sdstuberCommented:
if http:#36502248  fails  please post table description and sample data that doesn't work as expected
0
 
awking00Connect With a Mentor Commented:
Try the attached.
query.txt
0
 
pascal_moiseAuthor Commented:
Both solutions succeed !
Thanks sdstuber and awking00
Many thanks for your help (all)
0
 
sdstuberCommented:
sorry I didn't get a chance to come back to this sooner

actually 36505975  does not work


there are lots of close-to-end-of-month failures

For example   If today is Feb 23,  and your birthday is Feb 25   then awking00's method will fail


select to_date('19990225','yyyymmdd'),mod(months_between(to_date('19990225','yyyymmdd'),trunc(to_date('20110223','yyyymmdd') + 7)),12)
from dual
where mod(months_between(to_date('19990225','yyyymmdd'),trunc(to_date('20110223','yyyymmdd') + 7)),12) between -.22580646 and 0
;
0
 
awking00Commented:
sdstuber,
You're right, it fails when the plus 7 carries the date into the next month, but the attached should work.
query.txt
0
 
awking00Commented:
Actually, that doesn't work either :-(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.