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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Who has a birthday next week?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 03/16/2007 :  08:25:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 01/07/2009 16:42:44

jassi.singh
Posting Yak Master

122 Posts

Posted - 10/10/2011 :  09:10:57  Show Profile  Visit jassi.singh's Homepage  Reply with Quote
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 - 05/01/2013 :  13:13:41  Show Profile  Reply with Quote
Awesome, thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000