Need help with MS SQL query

[Fred Thompson]

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.

[Greg D. Moore \(Strider\)]

"Fred Thompson" <fr***@aol.co m> wrote in message
news:DISAb.4569 81$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]

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)" <mo*****@greenm s.com> wrote in message
news:OP******** *************@t wister.nyroc.rr .com...

"Fred Thompson" <fr***@aol.co m> wrote in message
news:DISAb.4569 81$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

[John Gilson]

"Fred Thompson" <fr***@aol.co m> wrote in message news:DISAb.4569 81$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_TIMESTA MP)
)

CREATE VIEW Today (d)
AS
SELECT CAST(CONVERT(CH AR(8), CURRENT_TIMESTA MP, 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 BirthdaysNDaysF romDate
(@ndays INT, @ref_date DATETIME = NULL)
RETURNS TABLE
AS
RETURN(
SELECT person_name,
birthday,
COALESCE(@ref_d ate, (SELECT d FROM Today)) AS reference_date
FROM Birthdays
WHERE birthday <=
COALESCE(@ref_d ate, (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_d ate, (SELECT d FROM Today)) AND
COALESCE(@ref_d ate, (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_d ate, (SELECT d FROM Today)) OR
DATEADD(YEAR,
YEAR(COALESCE(@ ref_date, (SELECT d FROM Today)) +
@ndays) - YEAR(birthday),
birthday) <=
COALESCE(@ref_d ate, (SELECT d FROM Today)) + @ndays)
)

-- Uses today as the reference date
CREATE FUNCTION BirthdaysNDaysF romToday (@ndays INT)
RETURNS TABLE
AS
RETURN(
SELECT *
FROM BirthdaysNDaysF romDate(@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 BirthdaysNDaysF romToday(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 BirthdaysNDaysF romDate(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

[Ronnie Chee]

"Fred Thompson" <fr***@aol.co m> wrote in message
news:DISAb.4569 81$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

[louis nguyen]

"Fred Thompson" <fr***@aol.co m> wrote in message news:<DISAb.456 981$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(d ay,@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)=da y(b.bdays)

[Igor Raytsin]

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" <fr***@aol.co m> wrote in message
news:DISAb.4569 81$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.

[tony_petruzzi]

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