See if a birthday is in the next 3 months? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

New to SQL Server Programming

See if a birthday is in the next 3 months?

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-10-06 : 13:39:13

I've spent a lot of time on this and can't get it to work.
Given a date of birth, I want to see if the birthday (month/day) for that person is coming up in the next 90 days. So today being October 5th, January 2 and December 25 should both be a match, regardless of the years.

Here's the code I have, which returns January 2 but not December 25:

select * from Person where
(dateadd(day,datepart(dy,BirthDate)-1,
dateadd( year, datediff(year,0,BirthDate)+
year(dateadd(dy,90,getdate()))-year(BirthDate),0)) <
dateadd( day, datediff(day,0,getdate() ),90))

Thank you in advance for a simpler way to do this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 13:47:17

how Jan 2 and Dec 25 will both come?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-10-06 : 13:51:17

I found the solution. Apparently this is a more common problem than I thought.
SELECT * FROM person WHERE Cast(DATEDIFF(dd, ProficiencyDate, getDate()) / 365.25 as int)
- Cast(DATEDIFF(dd, ProficiencyDate, DATEADD(dy,90,getdate()))
/ 365.25 as int) <> 0

amnak
Starting Member

3 Posts

Posted - 2011-10-06 : 13:57:02

select * from person
where birthdate > getdate() and birthday < getdate() + 90

this returs people that has bday in next 90 days.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-06 : 15:32:01

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80709

N 56°04'39.26"
E 12°55'05.63"

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-10 : 09:11:24

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources