ReturnRecordsBasedOnDateCalculations

[gh]

I am using SQL Server 2005 and I need to select records based on the
persons 50th birthday. The range of months I am using to calculate this
falls 3 months prior to August 22(May 22) to September 1st of the next
year. So if they have an 50th birthday in this window then I need the
persons record. May 22, 2009 to September 1, 2010 for the range to
calculate the birthday. The birthdate is stored as a datetime like this
4/21/1981 12:00:00 AM

Can this be done in one SQL statement? What about leap year?

Thanks

[Simon Whale]

what information do we have to work from? DOB (date of birth)? or year of
birth?

Many Thanks
Simon

"gh" <g...@att.net> wrote in message
news:uEHL6vSa...@TK2MSFTNGP05.phx.gbl...

[gh]

Simon:

The birthdate(DOB) is stored as 4/21/1981 12:00:00 AM, in the table. I
am selecting name, bdate and a clientcode. So for the current year we
would want all birthdates = 50 that fall between 5/22/2009 to 9/1/2010.
I would need to be able to set the ranges based on the current year.
So next year would be 5/22/2010 t0 9/1/2011. I am pretty sure a
datediff is what I need to use but not sure how to put it all together.
Is this what you wanted to know?

Thanks

[--CELKO--]

>>  I am pretty sure a DATEDIFF is what I need to use but not sure how to put it all together. <<

Why not just build a simple look up table of the start and end dates
for 10 or 20 years and use a BETWEEN predicate? You can do this in a
spreadsheet in nothing flat

CREATE TABLE Fifty_Year_Olds
(base_year INTEGER NOT NULL PRIMARY KEY,
dob_start_date DATETIME NOT NULL,
dob_end_date DATETIME NOT NULL,
CHECK (dob_start_date < dob_end_date));

SELECT O.base_year, F.*
FROM Fifty_Year_Olds AS O, Foobar AS F
WHERE F.birth_date BETWEEN O.dob_start_date AND O.dob_end_date
AND DATEPART(YEAR, CURRENT_TIMESTAMP)
IN (O.base_year, O.base_year+1);