quote:

---

Originally posted by visakh16

SELECT * FROM [dbo].[tbl_EMPXYZ] 
WHERE MONTH(DOB) BETWEEN MONTH(GETDATE()) AND MONTH(DATEADD(dd,20,GETDATE())) AND   
DAY(DOB) BETWEEN DAY(GETDATE()) AND 
DAY(DATEADD(dd,20,GETDATE())) 

---

quote:

---

Originally posted by raky

Declare @fromdate datetime, @todate datetime

select @fromdate = Getdate(), @todate = Getdate()+20
select @fromdate,@todate

select E_code,FirstName,MiddleName,LastName,DOB,BirthCountry,Nationality
from dbo.tbl_EMPXYZ
WHERE ( (@fromdate IS NULL OR CAST( CONVERT( VARCHAR(12), DOB, 101 ) AS DATETIME ) >= @fromdate )
AND ( @Todate IS NULL OR CAST( CONVERT( VARCHAR(12), DOB, 101 ) AS DATETIME ) <= @Todate ) )

---

quote:

---

Originally posted by jsmith8858

The easiest way is to manipulate the birthday to that it is a date in 2000 (a leap year), and then get your start/end dates in 2000 as well, and then compare.

so, to set the DOB so it is a date in 2000, you can use DateAdd():

select dateadd(year, 2000 - year(dob), DOB) as DOB_In_2000
from tbl_EMPXYZ


Now, just figure out your start/end dates in the year 2000:

declare @start datetime
declare @end date

set @start = dateadd(year, 2000-year(getdate()), getdate())
set @end = @start + 20

and from there you can write your sql like this:

select * from yourtable
where dateadd(year, 2000 - year(dob), DOB) between @start and @end

That's one basic way of doing it. tweak as needed.

We must use a leap year because if any dates in our calculations fall on Feb 29th, it may cause issues.



- Jeff
http://weblogs.sqlteam.com/JeffS

---

quote:

---

Originally posted by sunil

I believe I saw following query from Peso in one of the post.

DECLARE @sample TABLE
(
dob datetime
)

INSERT INTO @sample (dob)
SELECT '19000831' UNION ALL
SELECT '19000901' UNION ALL
SELECT '19000902' UNION ALL
SELECT '19000903' UNION ALL
SELECT '19000925'

SELECT *
FROM
(
SELECT *,
bd_this_year = DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, dob), dob)
FROM @sample s
) d
WHERE d.bd_this_year > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND d.bd_this_year <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 20)

Hope this helps.

---

quote:

---

Originally posted by Peso

[quote]Originally posted by sunil
Unbelivable what people find these days...

Too bad I can't take credit for this.

khtan wrote it here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109812

---

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

---

create function [dbo].[LastAnniversaryAsOf](@Anniversary datetime, @AsOfDate datetime)
returns datetime as
begin
/*
------------------------------------------------------------------------------------------------------------------------
FUNCTION:	LastAnniversaryAsOf
DESCRIPTION:	Returns the last anniversary as of a specified date.
------------------------------------------------------------------------------------------------------------------------
REVISION HISTORY:
EDITOR		DATE		REVISIONS
blindman	10/3/2007	Function created
------------------------------------------------------------------------------------------------------------------------
*/

--Test parameters
--declare	@Anniversary datetime
--declare	@AsOfDate datetime
--set	@Anniversary = '6/10/1965'
--set	@AsOfDate = getdate()

return
(select	max(Anniversary) as LastAnniversary
from
(select	dateadd(year, datediff(year, @Anniversary, @AsOfDate)-1, @Anniversary) as Anniversary
UNION
select	dateadd(year, datediff(year, @Anniversary, @AsOfDate), @Anniversary) as Anniversary) Candidates
where	Anniversary < @AsOfDate)

end
go

create function [dbo].[NextAnniversaryAsOf](@Anniversary datetime, @AsOfDate datetime)
returns datetime as
begin
/*
------------------------------------------------------------------------------------------------------------------------
FUNCTION:	NextAnniversaryAsOf
DESCRIPTION:	Returns the Next anniversary as of a specified date.
------------------------------------------------------------------------------------------------------------------------
REVISION HISTORY:
EDITOR		DATE		REVISIONS
blindman	10/3/2007	Function created
------------------------------------------------------------------------------------------------------------------------
*/

--Test parameters
--declare	@Anniversary datetime
--declare	@AsOfDate datetime
--set	@Anniversary = '6/10/1965'
--set	@AsOfDate = getdate()

return
(select	min(Anniversary) as NextAnniversary
from
(select	dateadd(year, datediff(year, @Anniversary, @AsOfDate)+1, @Anniversary) as Anniversary
UNION
select	dateadd(year, datediff(year, @Anniversary, @AsOfDate), @Anniversary) as Anniversary) Candidates
where	Anniversary > @AsOfDate)

end

declare @days int
set @days = 20
select *
from tbl_EMPXYZ
where 365 - datediff(dd, dob, getdate()) % 365 < @days

quote:

---

Originally posted by pankaj.pareek@sparrowi.co

Hi Jeff,

Not Working Properly, working only next three month.......

changed the system date '26-09-2008' to '26-12-2008' & check not working.

Regards
Rishi Sharma

---

quote:

---

Originally posted by mcrowley

This seems to give a rough idea of birthdays in the next 20 days, but it does drift with leap years. How accurate you need this to be is a question...

declare @days int
set @days = 20
select *
from tbl_EMPXYZ
where 365 - datediff(dd, dob, getdate()) % 365 < @days

---