Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Who has a birthday next week?

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 08:25:04
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 / 12,
@To = CASE
WHEN DATEPART(DAY, @DoB) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @DoB, @ToDate) - 1
ELSE DATEDIFF(MONTH, @DoB, @ToDate)
END / 12

RETURN NULLIF(@To, @From)
END
This is an example of how you can use the function
SELECT	DateOfBirth,
dbo.fnHasBirthday(DateOfBirth, '20070225', '20070303')
FROM Employees

Peter Larsson
Helsingborg, Sweden

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-10 : 09:10:57
DROP TABLE #person
CREATE TABLE #person(person INT, bdate DATETIME)
INSERT INTO #person
(person, bdate)
SELECT 1,'8-1-1986' UNION ALL
SELECT 2,'4-1-1985' UNION ALL
SELECT 3,'1-1-1985' UNION ALL
SELECT 4,'11-1-1985' UNION ALL
SELECT 5,'12-1-1985' UNION ALL
SELECT 6,'2-1-1985' UNION ALL
SELECT 7,'1-2-1985' UNION ALL
SELECT 8,'1-10-1985'

WITH cte_withcurrentyearbirthdate(person, currentyeardate)
AS (SELECT
PERSON,
DATEADD(YEAR, DATEDIFF(year, bdate, GETDATE()), bdate) currentyeardate
FROM
#person
),
cteFuture(person, currentyeardate, commingBirthDate)
AS (SELECT
Person, currentyeardate,
CASE WHEN currentyeardate < GETDATE()
THEN DATEADD(YEAR, 1, currentyeardate)
ELSE currentyeardate
END AS commingBirthDate
FROM
cte_withcurrentyearbirthdate
)
SELECT
person,commingbirthdate
FROM
ctefuture
WHERE
commingBirthDate > GETDATE()
AND DATEADD(mm, 3, GETDATE()) > commingBirthDate ORDER BY commingbirthdate asc



Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh
Go to Top of Page

offspring22
Starting Member

38 Posts

Posted - 2013-05-01 : 13:13:41
Awesome, thanks!
Go to Top of Page
   

- Advertisement -