"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