SEO

I'm using MS SQL 2000 server. I have a table which includes a date field
which has people's birthdays in it. How can I write a query to return all
the records of people with birthdays within the next 30 days? (Based on
system date as the starting point.) Actual year of birthday is irrelevant.
Also the next 30 days may span into the next month, or the next year.

"Fred Thompson" <fredr@aol.com> wrote in message
newsISAb.456981$HS4.3574769@attbi_s01...
> I'm using MS SQL 2000 server. I have a table which includes a date field
> which has people's birthdays in it. How can I write a query to return all
> the records of people with birthdays within the next 30 days? (Based on
> system date as the starting point.) Actual year of birthday is
irrelevant.
> Also the next 30 days may span into the next month, or the next year.
>

Something like

select lname, birthdate from birthtable where birthdate> getdate() and
birthdate< getdate()+30


>

But this takes year into account, so your query will only select people born
30 days into the future.
I need the query to select anyone whose birthday is coming up in the next 30
days regardless of what year they were born.

"Greg D. Moore (Strider)" <mooregr@greenms.com> wrote in message
news:OPTAb.191750$1N3.128360@twister.nyroc.rr.com. ..
>
> "Fred Thompson" <fredr@aol.com> wrote in message
> newsISAb.456981$HS4.3574769@attbi_s01...
> > I'm using MS SQL 2000 server. I have a table which includes a date
field
> > which has people's birthdays in it. How can I write a query to return
all
> > the records of people with birthdays within the next 30 days? (Based on
> > system date as the starting point.) Actual year of birthday is
> irrelevant.
> > Also the next 30 days may span into the next month, or the next year.
> >
>
> Something like
>
> select lname, birthdate from birthtable where birthdate> getdate() and
> birthdate< getdate()+30
>
>
> >
>
>

"Fred Thompson" <fredr@aol.com> wrote in message newsISAb.456981$HS4.3574769@attbi_s01...
> I'm using MS SQL 2000 server. I have a table which includes a date field
> which has people's birthdays in it. How can I write a query to return all
> the records of people with birthdays within the next 30 days? (Based on
> system date as the starting point.) Actual year of birthday is irrelevant.
> Also the next 30 days may span into the next month, or the next year.

Here's a UDF that will find all birthdays N days from a given reference date.
For your case, N = 30 and the reference date = today.

CREATE TABLE Birthdays
(
person_name VARCHAR(25) NOT NULL PRIMARY KEY,
birthday DATETIME NOT NULL CHECK (birthday <= CURRENT_TIMESTAMP)
)

CREATE VIEW Today (d)
AS
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS
DATETIME)

-- Returns all persons whose birthday is within @ndays of @ref_date
-- Assumes @ref_date has a time of 12AM
-- Default @ref_date is today
CREATE FUNCTION BirthdaysNDaysFromDate
(@ndays INT, @ref_date DATETIME = NULL)
RETURNS TABLE
AS
RETURN(
SELECT person_name,
birthday,
COALESCE(@ref_date, (SELECT d FROM Today)) AS reference_date
FROM Birthdays
WHERE birthday <=
COALESCE(@ref_date, (SELECT d FROM Today)) + @ndays AND
(YEAR(COALESCE(@ref_date, (SELECT d FROM Today))) <
YEAR(COALESCE(@ref_date, (SELECT d FROM Today)) + @ndays) OR
(DATEADD(YEAR,
YEAR(COALESCE(@ref_date, (SELECT d FROM Today))) -
YEAR(birthday),
birthday) BETWEEN
COALESCE(@ref_date, (SELECT d FROM Today)) AND
COALESCE(@ref_date, (SELECT d FROM Today)) + @ndays)) AND
(YEAR(COALESCE(@ref_date, (SELECT d FROM Today))) =
YEAR(COALESCE(@ref_date, (SELECT d FROM Today)) + @ndays) OR
DATEADD(YEAR,
YEAR(COALESCE(@ref_date,
(SELECT d FROM Today))) - YEAR(birthday),
birthday) >=
COALESCE(@ref_date, (SELECT d FROM Today)) OR
DATEADD(YEAR,
YEAR(COALESCE(@ref_date, (SELECT d FROM Today)) +
@ndays) - YEAR(birthday),
birthday) <=
COALESCE(@ref_date, (SELECT d FROM Today)) + @ndays)
)

-- Uses today as the reference date
CREATE FUNCTION BirthdaysNDaysFromToday (@ndays INT)
RETURNS TABLE
AS
RETURN(
SELECT *
FROM BirthdaysNDaysFromDate(@ndays, DEFAULT)
)

INSERT INTO Birthdays (person_name, birthday)
VALUES ('Joe', '19801231')
INSERT INTO Birthdays (person_name, birthday)
VALUES ('Jim', '20000101')
INSERT INTO Birthdays (person_name, birthday)
VALUES ('Jerry', '19500610')

-- All birthdays 30 days from today, 20031208
SELECT *
FROM BirthdaysNDaysFromToday(30)

person_name birthday reference_date
Jim 2000-01-01 00:00:00.000 2003-12-08 00:00:00.000
Joe 1980-12-31 00:00:00.000 2003-12-08 00:00:00.000

-- All birthdays 180 days from 19811215
-- Given this reference date, Jim wasn't born yet
SELECT *
FROM BirthdaysNDaysFromDate(180, '19811215')

person_name birthday reference_date
Jerry 1950-06-10 00:00:00.000 1981-12-15 00:00:00.000
Joe 1980-12-31 00:00:00.000 1981-12-15 00:00:00.000

Regards,
jag

"Fred Thompson" <fredr@aol.com> wrote in message
newsISAb.456981$HS4.3574769@attbi_s01...
> I'm using MS SQL 2000 server. I have a table which includes a date field
> which has people's birthdays in it. How can I write a query to return all
> the records of people with birthdays within the next 30 days? (Based on
> system date as the starting point.) Actual year of birthday is
irrelevant.
> Also the next 30 days may span into the next month, or the next year.
>
>
Here is a half-baked idea (totally untested as well):

select lname, birthdate
from birthdaytable
where datediff(year, birthdate, getdate()) < datediff(year, birthdate,
dateadd(day,30, getdate()))

Assuming datediff(year, ...) rounds down.
I think that this will be a poor performer in that it should force a table
scan but depending on how large the birthday table is, this may be
irrelevant.

Hope this helps
Ronnie

"Fred Thompson" <fredr@aol.com> wrote in message news:<DISAb.456981$HS4.3574769@attbi_s01>...
> I'm using MS SQL 2000 server. I have a table which includes a date field
> which has people's birthdays in it. How can I write a query to return all
> the records of people with birthdays within the next 30 days? (Based on
> system date as the starting point.) Actual year of birthday is irrelevant.
> Also the next 30 days may span into the next month, or the next year.
Hi Fred,

The simplest way I can think of is create a temp table with the next
30 days and join it against your birthday table on month()=month() and
day()=day(). The code would be like:

create table #T(bdays datetime)
declare @i int, @d datetime
select @d=getdate()
select @d=cast(year(@d) as varchar)
+'/'+cast(month(@d) as varchar)
+'/'+cast(day(@d) as varchar)

set @i=0
while @i<=30 begin
insert into #T
select bdays=dateadd(day,@i,@d)
set @i=@i+1
end

select a.bdays
from birthdays as a
join #T as b
on month(a.bdays)=month(b.bdays)
and day(a.bdays)=day(b.bdays)

I didn't test it, but you'll get the idea:

SELECT Name, birthday, DATEDIFF(yy, birthday, getdate()) as years_now,
DATEDIFF(yy, birthday, (getdate() +30) ) as years_will_be
FROM table
WHERE DATEDIFF(yy, birthday, getdate()) < DATEDIFF(yy, birthday, (getdate()
+30) )


-----
Hope this helps


"Fred Thompson" <fredr@aol.com> wrote in message
newsISAb.456981$HS4.3574769@attbi_s01...
> I'm using MS SQL 2000 server. I have a table which includes a date field
> which has people's birthdays in it. How can I write a query to return all
> the records of people with birthdays within the next 30 days? (Based on
> system date as the starting point.) Actual year of birthday is
irrelevant.
> Also the next 30 days may span into the next month, or the next year.
>
>

select *
from table
where datediff(day, getdate(), dateadd(year, (year(getdate()) -
year(birthday)), birthday)) < 30