| Author |
 Topic  |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 03/16/2007 : 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 functionSELECT 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
|
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
36 Posts |
Posted - 05/01/2013 : 13:13:41
|
| Awesome, thanks! |
|
|
| |
Topic |
|
|
|
Who has a birthday next week?
