Who has a birthday next week? - SQL Server Forums

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

offspring22
Starting Member

38 Posts

Posted - 2013-05-01 : 13:13:41

Awesome, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources