Solved

oracle sql query date

Posted on 2011-09-08
23
908 Views
Last Modified: 2012-05-12
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
Comment
Question by:pascal_moise
  • 6
  • 5
  • 5
  • +3
23 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
 
LVL 6

Expert Comment

by:akajohn
Comment Utility
select peopleid  from PEOPLE where mod(PEOPLE.BIRTHDATE-SYSDATE,365)<8


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


A>
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
SELECT peopleid  FROM the_table
WHERE    TO_NUMBER (birtday, 'DDD)   BETWEEN   TO_NUMBER (sysdate, 'DDD') AND TO_NUMBER (sysdate+7, 'DDD') ;
0
 
LVL 6

Expert Comment

by:akajohn
Comment Utility
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
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
 

Author Comment

by:pascal_moise
Comment Utility
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
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
have you tried my solution ?
0
 

Author Comment

by:pascal_moise
Comment Utility
pratima
yes just now and receive error "your entry cannot be converted to a valid date time value"
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pascal_moise
Comment Utility
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
Comment Utility
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
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
 

Author Comment

by:pascal_moise
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
every suggestion above, whether correct or not, should be able to execute in 8i (barring other minor syntax errors like missing quotes)
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if http:#36502248  fails  please post table description and sample data that doesn't work as expected
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 200 total points
Comment Utility
Try the attached.
query.txt
0
 

Author Closing Comment

by:pascal_moise
Comment Utility
Both solutions succeed !
Thanks sdstuber and awking00
Many thanks for your help (all)
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Actually, that doesn't work either :-(
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now