Home » SQL & PL/SQL » SQL & PL/SQL » date range
date range [message #194083] Wed, 20 September 2006 14:19 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hi guys,

I'm trying to write a query to determine whether somebody's birthday falls between a given date range.

Birthday means that year doesn't matter. So for example, if the user inputted May 12, 2006 and December 15, 2006, I will need to find out all the birthdays that fall between those two dates.

Can somebody lead me in the right direction?

select  p.first_name,
        p.last_name,
        p.birth_date
from    person p
where   p.birth_date between from_date and to_date


The above code won't work as it takes the year into consideration.

Thanks,
John.
Re: date range [message #194093 is a reply to message #194083] Wed, 20 September 2006 14:58 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
subtract the date from trunc(date,'YYYY') and you will get number of days from start of the relevant year. do that for both birthdays and the referent dates, and compare if the number of days for the birth date is between two referent numbers...

example:

select sysdate - trunc(sysdate,'YYYY') from dual

will give you the number of days (don't be confused with decimals, they represent time) between 1/1/2006 and current date.

Gojko Adzic
http://www.gojko.com
Re: date range [message #194096 is a reply to message #194083] Wed, 20 September 2006 15:26 Go to previous messageGo to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
hi,

thank you for the reply, but I'm not sure if the above solution will work... if for example a user inputted May 1, 2005 to March 31, 2006, but a person's birthday can be January 15, 1985.

May 1, 2005 - 120 days
Narch 31, 2006 - 89 days
January 15 - 14 days
Re: date range [message #194100 is a reply to message #194083] Wed, 20 September 2006 15:43 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
select  p.first_name,
        p.last_name,
        p.birth_date
from    person p
where   p.birth_date between to_date('&fromdate','Month DD, YYYY') and to_date ('&todate','Month DD, YYYY')
/
Re: date range [message #194101 is a reply to message #194096] Wed, 20 September 2006 15:51 Go to previous message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
Ok, then just move the birth date to the year of from_date

You can do something like this

select p.first_name,
p.last_name,
p.birth_date
from person p
where p.birth_date - trunc(p.birth_date,'YYYY') + trunc(from_date,'YYYY') between from_date and to_date

Gojko Adzic
http://www.gojko.com
Previous Topic: DYNAMIC SQL -- How does one dynamically provide the INTO value?
Next Topic: CONSTRAINT (ENABLE VALIDATE)
Goto Forum:
  


Current Time: Sat Dec 03 03:46:14 CST 2016

Total time taken to generate the page: 0.14211 seconds