oracle sql query date

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') ;

select peopleid  from PEOPLE where mod(PEOPLE.BIRTHDATE-SYSDATE,365)<8


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


A>

SELECT peopleid  FROM the_table
WHERE    TO_NUMBER (birtday, 'DDD)   BETWEEN   TO_NUMBER (sysdate, 'DDD') AND TO_NUMBER (sysdate+7, 'DDD') ;

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.

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'))))

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

have you tried my solution ?

pratima
yes just now and receive error "your entry cannot be converted to a valid date time value"

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

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

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'))))

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

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'))

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

every suggestion above, whether correct or not, should be able to execute in 8i (barring other minor syntax errors like missing quotes)

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

if http:#36502248  fails  please post table description and sample data that doesn't work as expected

Both solutions succeed !
Thanks sdstuber and awking00
Many thanks for your help (all)

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
;

sdstuber,
You're right, it fails when the plus 7 carries the date into the next month, but the attached should work.
query.txt

Actually, that doesn't work either :-(