• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 979
  • Last Modified:

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

0
pascal_moise
Asked:
pascal_moise
  • 6
  • 5
  • 5
  • +3
2 Solutions
 
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
 
akajohnCommented:
select peopleid  from PEOPLE where mod(PEOPLE.BIRTHDATE-SYSDATE,365)<8


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


A>
0
 
schwertnerCommented:
SELECT peopleid  FROM the_table
WHERE    TO_NUMBER (birtday, 'DDD)   BETWEEN   TO_NUMBER (sysdate, 'DDD') AND TO_NUMBER (sysdate+7, 'DDD') ;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
akajohnCommented:
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
 
sdstuberCommented:
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
 
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
 
awking00Commented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 5
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now