Who Has A Birthday Next Week?

With reference to this topic, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
you can now easy calculate if someone has a birthday within any time period!
If there is a birthday, the function returns how many years the subject will be, otherwise it returns NULL.CREATE FUNCTION dbo.fnHasBirthday
(
@DoB DATETIME,
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE@From INT,
@To INT

IF @DoB IS NULL OR @FromDate IS NULL OR @ToDate IS NULL OR @FromDate > @ToDate
RETURN NULL

SELECT@From =CASE
WHEN DATEPART(DAY, @DoB) >= DATEPART(DAY, @FromDate) THEN DATEDIFF(MONTH, @DoB, @FromDate) - 1
ELSE DATEDIFF(MONTH, @DoB, @FromDate)
END,
@To =CASE
WHEN DATEPART(DAY, @DoB) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @DoB, @ToDate) - 1
ELSE DATEDIFF(MONTH, @DoB, @ToDate)
END

RETURNNULLIF(@To, @From) / 12
ENDThis is an example of how you can use the functionSELECTDateOfBirth,
dbo.fnHasBirthday(DateOfBirth, '20070225', '20070303')
FROMEmployees

Peter Larsson
Helsingborg, Sweden