query involves dates

Hi,

I am trying to get the following from a customer table:
A list of all customers under 10 that have a birthday in the next
week.

I tried it and I think the first part is ok (unless I am mistaken!)
but I don't really know how to do "under 10 that have a birthday in
the next week".

select first_name, last_name, dob
from customer
where dob < current_date + interval 10 year;

Thanks in advance.
Ros

On 3 Apr, 11:41, "roohbir" <ros...@gmail.com> wrote:
> Hi,
>
> I am trying to get the following from a customer table:
> A list of all customers under 10 that have a birthday in the next
> week.
>
> I tried it and I think the first part is ok (unless I am mistaken!)
> but I don't really know how to do "under 10 that have a birthday in
> the next week".
>
> select first_name, last_name, dob
> from customer
> where dob < current_date + interval 10 year;
>
> Thanks in advance.
> Ros

Well, since current_date + 10 years is in the future, anyone who has
already been born satisfies that condition!

The query below assumes that "in the next week", includes today.

SELECT
`first_name`,
`last_name`,
`dob`
FROM `customer`
WHERE `dob` > CURRENT_DATE - INTERVAL 10 YEAR
AND `dob` BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY

On 3 Apr, 14:30, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Captain Paralytic wrote:
> > On 3 Apr, 11:41, "roohbir" <ros...@gmail.com> wrote:
> >> Hi,
>
> >> I am trying to get the following from a customer table:
> >> A list of all customers under 10 that have a birthday in the next
> >> week.
>
> >> I tried it and I think the first part is ok (unless I am mistaken!)
> >> but I don't really know how to do "under 10 that have a birthday in
> >> the next week".
>
> >> select first_name, last_name, dob
> >> from customer
> >> where dob < current_date + interval 10 year;
>
> >> Thanks in advance.
> >> Ros
>
> > Well, since current_date + 10 years is in the future, anyone who has
> > already been born satisfies that condition!
>
> > The query below assumes that "in the next week", includes today.
>
> > SELECT
> > `first_name`,
> > `last_name`,
> > `dob`
> > FROM `customer`
> > WHERE `dob` > CURRENT_DATE - INTERVAL 10 YEAR
> > AND `dob` BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
>
> Close - but it will only return those born in the next week (BETWEEN
> CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY).
>
> I guess you could always "brute force" it - not pretty:
>
> SELECT `first_name`, `last_name`, `dob`
> FROM `customer`
> WHERE `dob` BETWEEN CURRENT_DATE - INTERVAL 10 YEAR AND CURRENT_DATE -
> INTERVAL 10 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 9 YEAR AND CURRENT_DATE - INTERVAL
> 9 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 8 YEAR AND CURRENT_DATE - INTERVAL
> 8 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 7 YEAR AND CURRENT_DATE - INTERVAL
> 7 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 6 YEAR AND CURRENT_DATE - INTERVAL
> 6 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 5 YEAR AND CURRENT_DATE - INTERVAL
> 5 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 4 YEAR AND CURRENT_DATE - INTERVAL
> 4 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 3 YEAR AND CURRENT_DATE - INTERVAL
> 3 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 2 YEAR AND CURRENT_DATE - INTERVAL
> 2 YEAR + INTERVAL 7 DAY OR
> `dob` BETWEEN CURRENT_DATE - INTERVAL 1 YEAR AND CURRENT_DATE - INTERVAL
> 1 YEAR + INTERVAL 7 DAY;
>
> Too bad there isn't a month_day function in MySQL.
>
> I also played around with dayofyear. It works fine with two exceptions
> - the last week of the year (which can be handled fairly easily), and
> leap years. The latter is a problem because everything after Feb. 28th
> will be off one day if either the current year or the dob year is a leap
> year, but not both. The latter started getting very complicated to handle.
>
> Another possibility which I haven't tried (and may have some errors)
> might be:
>
> SELECT `first_name`, `last_name`, `dob`
> FROM `customer`
> WHERE `dob` > CURRENT_DATE = INTERVAL 10 YEAR AND
> `dob` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`dob`)) YEAR BETWEEN
> CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
>
> But it should work
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Good point

Captain Paralytic wrote:
> On 3 Apr, 11:41, "roohbir" <ros...@gmail.com> wrote:
>> Hi,
>>
>> I am trying to get the following from a customer table:
>> A list of all customers under 10 that have a birthday in the next
>> week.
>>
>> I tried it and I think the first part is ok (unless I am mistaken!)
>> but I don't really know how to do "under 10 that have a birthday in
>> the next week".
>>
>> select first_name, last_name, dob
>> from customer
>> where dob < current_date + interval 10 year;
>>
>> Thanks in advance.
>> Ros
>
> Well, since current_date + 10 years is in the future, anyone who has
> already been born satisfies that condition!
>
> The query below assumes that "in the next week", includes today.
>
> SELECT
> `first_name`,
> `last_name`,
> `dob`
> FROM `customer`
> WHERE `dob` > CURRENT_DATE - INTERVAL 10 YEAR
> AND `dob` BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
>

Close - but it will only return those born in the next week (BETWEEN
CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY).

I guess you could always "brute force" it - not pretty:

SELECT `first_name`, `last_name`, `dob`
FROM `customer`
WHERE `dob` BETWEEN CURRENT_DATE - INTERVAL 10 YEAR AND CURRENT_DATE -
INTERVAL 10 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 9 YEAR AND CURRENT_DATE - INTERVAL
9 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 8 YEAR AND CURRENT_DATE - INTERVAL
8 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 7 YEAR AND CURRENT_DATE - INTERVAL
7 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 6 YEAR AND CURRENT_DATE - INTERVAL
6 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 5 YEAR AND CURRENT_DATE - INTERVAL
5 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 4 YEAR AND CURRENT_DATE - INTERVAL
4 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 3 YEAR AND CURRENT_DATE - INTERVAL
3 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 2 YEAR AND CURRENT_DATE - INTERVAL
2 YEAR + INTERVAL 7 DAY OR
`dob` BETWEEN CURRENT_DATE - INTERVAL 1 YEAR AND CURRENT_DATE - INTERVAL
1 YEAR + INTERVAL 7 DAY;

Too bad there isn't a month_day function in MySQL.

I also played around with dayofyear. It works fine with two exceptions
- the last week of the year (which can be handled fairly easily), and
leap years. The latter is a problem because everything after Feb. 28th
will be off one day if either the current year or the dob year is a leap
year, but not both. The latter started getting very complicated to handle.

Another possibility which I haven't tried (and may have some errors)
might be:

SELECT `first_name`, `last_name`, `dob`
FROM `customer`
WHERE `dob` > CURRENT_DATE = INTERVAL 10 YEAR AND
`dob` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`dob`)) YEAR BETWEEN
CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY

But it should work



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news:3O6dnZU34-Kt1I_bnZ2dnUVZ_riknZ2d@comcast.com:


> Too bad there isn't a month_day function in MySQL.

But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')

--
felix

On 3 Apr, 16:03, Felix Geerinckx <felix.geerin...@gmail.com> wrote:
> Jerry Stuckle <jstuck...@attglobal.net> wrote innews:3O6dnZU34-Kt1I_bnZ2dnUVZ_riknZ2d@comcast.com:
>
> > Too bad there isn't a month_day function in MySQL.
>
> But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')
>
> --
> felix

Yes, but it is no help in this case as 1230 will be greater than 0101.

On Apr 3, 8:22 am, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 3 Apr, 16:03, Felix Geerinckx <felix.geerin...@gmail.com> wrote:
>
> > Jerry Stuckle <jstuck...@attglobal.net> wrote innews:3O6dnZU34-Kt1I_bnZ2dnUVZ_riknZ2d@comcast.com:
>
> > > Too bad there isn't a month_day function in MySQL.
>
> > But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')
>
> > --
> > felix
>
> Yes, but it is no help in this case as 1230 will be greater than 0101.

Thanks folks. That fixed the problem.
Cheers
ros

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in
news:1175613726.568556.147060@q75g2000hsh.googlegr oups.com:

> On 3 Apr, 16:03, Felix Geerinckx <felix.geerin...@gmail.com> wrote:
>> Jerry Stuckle <jstuck...@attglobal.net> wrote
>> innews:3O6dnZU34-Kt1I_bnZ2dnUVZ_riknZ2d@comcast.com:
>>
>> > Too bad there isn't a month_day function in MySQL.
>>
>> But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')
>>
>> --
>> felix
>
> Yes, but it is no help in this case as 1230 will be greater than 0101.
>

But it allows you to easily calculate someone's age at a certain date:

SELECT
YEAR(@adate) - YEAR(dob) -
(DATE_FORMAT(@adate, '%m%d') < DATE_FORMAT(dob, '%m%d')) AS Age
FROM ...


--
felix