SEO

#1 (**permalink**) 02-28-2008, 10:32 AM

How do I perform a select statement that would locate all records in
which the day and month are in the next say 30 days regardless of the
year. Basically an upcoming birthday/anniversary list.

Thanks, Carolyn

#2 (**permalink**) 02-28-2008, 10:32 AM

On Jan 29, 11:31 pm, Carolyn Marenger <caj...@marenger.com> wrote:
> How do I perform a select statement that would locate all records in
> which the day and month are in the next say 30 days regardless of the
> year. Basically an upcoming birthday/anniversary list.
>
> Thanks, Carolyn

Have a look at the function date_sub(), and you find more date
functions here http://dev.mysql.com/doc/refman/5.1/...functions.html.

#3 (**permalink**) 02-28-2008, 10:32 AM

Kim wrote:
> On Jan 29, 11:31 pm, Carolyn Marenger <caj...@marenger.com> wrote:
>> How do I perform a select statement that would locate all records in
>> which the day and month are in the next say 30 days regardless of the
>> year. Basically an upcoming birthday/anniversary list.
>>
>> Thanks, Carolyn
>
> Have a look at the function date_sub(), and you find more date
> functions here http://dev.mysql.com/doc/refman/5.1/...functions.html.

I don't think I can use date_sub() for it, but extract looks like it a
close match to my needs.

Carolyn

#4 (**permalink**) 02-28-2008, 10:32 AM

"Carolyn Marenger" <cajunk@marenger.com> schreef in bericht
news:44b9a$47a078eb$cf70133e$2109@PRIMUS.CA...
> Kim wrote:
>> On Jan 29, 11:31 pm, Carolyn Marenger <caj...@marenger.com> wrote:
>>> How do I perform a select statement that would locate all records in
>>> which the day and month are in the next say 30 days regardless of the
>>> year. Basically an upcoming birthday/anniversary list.
>>>
>>> Thanks, Carolyn
>>
>> Have a look at the function date_sub(), and you find more date
>> functions here
>> http://dev.mysql.com/doc/refman/5.1/...functions.html.
>
> I don't think I can use date_sub() for it, but extract looks like it a
> close match to my needs.
>
> Carolyn

select now(), date_add(now(), INTERVAL 30 DAY);
or
select now(), date_sub(now(), INTERVAL -30 DAY);

gives:
2008-01-30 14:38:29 2008-02-29 14:38:29

i think this will be easier than the extract function ?

#5 (**permalink**) 02-28-2008, 10:32 AM

Luuk wrote:
> "Carolyn Marenger" <cajunk@marenger.com> schreef in bericht
> news:44b9a$47a078eb$cf70133e$2109@PRIMUS.CA...
>> Kim wrote:
>>> On Jan 29, 11:31 pm, Carolyn Marenger <caj...@marenger.com> wrote:
>>>> How do I perform a select statement that would locate all records in
>>>> which the day and month are in the next say 30 days regardless of the
>>>> year. Basically an upcoming birthday/anniversary list.
>>>>
>>>> Thanks, Carolyn
>>> Have a look at the function date_sub(), and you find more date
>>> functions here
>>> http://dev.mysql.com/doc/refman/5.1/...functions.html.
>> I don't think I can use date_sub() for it, but extract looks like it a
>> close match to my needs.
>>
>> Carolyn
>
> select now(), date_add(now(), INTERVAL 30 DAY);
> or
> select now(), date_sub(now(), INTERVAL -30 DAY);
>
> gives:
> 2008-01-30 14:38:29 2008-02-29 14:38:29
>
>
> i think this will be easier than the extract function ?
>

That gives me dates for this year, but I want to get a listing of all
people with birthdays in the next 30 days. So, today being January
30th, I want a listing of all people with birthdays from today, 01-30 to
03-02 for ALL years, not just the current year.

As I understood date_add(), it would only give me people born today and
in the next 30 days. Am I mistaken?

#6 (**permalink**) 02-28-2008, 10:32 AM

"Carolyn Marenger" <cajunk@marenger.com> schreef in bericht
news:c700e$47a08217$cf70133e$13696@PRIMUS.CA...
> Luuk wrote:
>> "Carolyn Marenger" <cajunk@marenger.com> schreef in bericht
>> news:44b9a$47a078eb$cf70133e$2109@PRIMUS.CA...
>>> Kim wrote:
>>>> On Jan 29, 11:31 pm, Carolyn Marenger <caj...@marenger.com> wrote:
>>>>> How do I perform a select statement that would locate all records in
>>>>> which the day and month are in the next say 30 days regardless of the
>>>>> year. Basically an upcoming birthday/anniversary list.
>>>>>
>>>>> Thanks, Carolyn
>>>> Have a look at the function date_sub(), and you find more date
>>>> functions here
>>>> http://dev.mysql.com/doc/refman/5.1/...functions.html.
>>> I don't think I can use date_sub() for it, but extract looks like it a
>>> close match to my needs.
>>>
>>> Carolyn
>>
>> select now(), date_add(now(), INTERVAL 30 DAY);
>> or
>> select now(), date_sub(now(), INTERVAL -30 DAY);
>>
>> gives:
>> 2008-01-30 14:38:29 2008-02-29 14:38:29
>>
>>
>> i think this will be easier than the extract function ?
>>
>
> That gives me dates for this year, but I want to get a listing of all
> people with birthdays in the next 30 days. So, today being January 30th,
> I want a listing of all people with birthdays from today, 01-30 to 03-02
> for ALL years, not just the current year.
>
> As I understood date_add(), it would only give me people born today and in
> the next 30 days. Am I mistaken?

you're right... i did not read your question correctly...

#7 (**permalink**) 02-28-2008, 10:32 AM

Carolyn Marenger wrote:
> How do I perform a select statement that would locate all records in
> which the day and month are in the next say 30 days regardless of the
> year. Basically an upcoming birthday/anniversary list.
>
> Thanks, Carolyn

I'm sure there's an easier way to do this, but here at least is one method.
Supposing the date field is anniversary:

SELECT
*
WHERE
DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
< 30

#8 (**permalink**) 02-28-2008, 10:32 AM

"Paul Lautman" <paul.lautman@btinternet.com> schreef in bericht
news:60bvnqF1msefuU1@mid.individual.net...
> Carolyn Marenger wrote:
>> How do I perform a select statement that would locate all records in
>> which the day and month are in the next say 30 days regardless of the
>> year. Basically an upcoming birthday/anniversary list.
>>
>> Thanks, Carolyn
>
> I'm sure there's an easier way to do this, but here at least is one
> method. Supposing the date field is anniversary:
>
> SELECT
> *
> WHERE
> DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
> < 30
>

select datediff(now(),'2008-03-31');
returns
-60

so,
SELECT
*
WHERE
ABS(DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
)
< 30

might work better...

#9 (**permalink**) 02-28-2008, 10:32 AM

On 31 Jan, 09:01, "Luuk" <l...@invalid.lan> wrote:
> "Paul Lautman" <paul.laut...@btinternet.com> schreef in berichtnews:60bvnqF1msefuU1@mid.individual.net...
>
>
>
> > Carolyn Marenger wrote:
> >> How do I perform a select statement that would locate all records in
> >> which the day and month are in the next say 30 days regardless of the
> >> year. Basically an upcoming birthday/anniversary list.
>
> >> Thanks, Carolyn
>
> > I'm sure there's an easier way to do this, but here at least is one
> > method. Supposing the date field is anniversary:
>
> > SELECT
> > *
> > WHERE
> > DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
> > < 30
>
> select datediff(now(),'2008-03-31');
> returns
> -60
>
> so,
> SELECT
> *
> WHERE
> ABS(DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
> )
> < 30
>
> might work better...

Since the query has NOW() in the second position, your query with
NOW() in the first position cannot occur (parameters do not swap
positions all by themselves).

#10 (**permalink**) 02-28-2008, 10:32 AM

"Captain Paralytic" <paul_lautman@yahoo.com> schreef in bericht
news:9a68a4dd-07df-4e0d-89af-12c851ca9177@q21g2000hsa.googlegroups.com...
> On 31 Jan, 09:01, "Luuk" <l...@invalid.lan> wrote:
>> "Paul Lautman" <paul.laut...@btinternet.com> schreef in
>> berichtnews:60bvnqF1msefuU1@mid.individual.net...
>>
>>
>>
>> > Carolyn Marenger wrote:
>> >> How do I perform a select statement that would locate all records in
>> >> which the day and month are in the next say 30 days regardless of the
>> >> year. Basically an upcoming birthday/anniversary list.
>>
>> >> Thanks, Carolyn
>>
>> > I'm sure there's an easier way to do this, but here at least is one
>> > method. Supposing the date field is anniversary:
>>
>> > SELECT
>> > *
>> > WHERE
>> > DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
>> > < 30
>>
>> select datediff(now(),'2008-03-31');
>> returns
>> -60
>>
>> so,
>> SELECT
>> *
>> WHERE
>> ABS(DATEDIFF(CONCAT(YEAR(NOW()),'-',MONTH(anniversary),'-',DAY(anniversary)),NOW())
>> )
>> < 30
>>
>> might work better...
>
> Since the query has NOW() in the second position, your query with
> NOW() in the first position cannot occur (parameters do not swap
> positions all by themselves).

ok, but
select datediff('2007-12-01',now());
also returns a negative number

so by next month, when now is i.e. 2008-02-20
this: select datediff('2008-01-01',now());
will also return a negative number...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

pulling upcoming dates