SQL Server Forums
Profile | Register | 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?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 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

36 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000